create database cda112;

use cda112;

create table student

(sno varchar(20), sname varchar(20), ssex char(10), sage int, sdept varchar(20));

insert into student values

("9512101","李勇","男",19,"計算機(jī)系"),

("9512102","劉晨","男",20,"計算機(jī)系"),

("9512103","王敏","女",20,"計算機(jī)系"),

("9521101","張立","男",22,"信息系"),

("9521102","吳賓","女",21,"信息系"),

("9521103","張海","男",20,"信息系"),

("9531101","錢小力","女",18,"數(shù)學(xué)系"),

("9531102","王大力","男",19,"數(shù)學(xué)系");

#（1）查詢哪些學(xué)生的年齡相同,要求列出年齡相同的學(xué)生的姓名和年齡。

#方法一:

select sname, sage

from student

where sage in (select sage from student group by sage having count(sage)>1 order by sage)

order by sage asc;

#方法二:

select t1.sname ,t1.sage

from (select sno,sname,sage ,count(sage) over (partition by sage) as sum1 from student)as t1

where sum1>1 order by t1.sage;

#方法三:

select sage 年齡 ,group_concat(sname) 姓名

from student

group by sage

having count(姓名)>1;

學(xué)習(xí)更多數(shù)據(jù)分析知識歡迎加入CDA網(wǎng)校會員 http://www.3lll3.cn/member.html?utm_source=weitao