扫码加入数据分析学习群

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

insert into student values

("9512101","李勇","男",19,"计算机系"),

("9512102","刘晨”,"男",20,"计算机系"),

("9512103","王敏","女",20,"计算机系"),

("9521101","张立","男.".22,"信息系"),

("9521102","吴宾","女",21."信息系")，

("9521103"."张海","男",20,"信息系"),

("9531101","钱小力","女",18,"数学系"),

("9531102","王大力","男",19,"数学系");

创建一张学生表如上所示,现要求查询哪些学生的年龄相同，并且列出年龄相同的学生的姓名和年龄。

#方法一:

select sname, sage

from student

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

圆括号中的子查询先对年龄分组,然后找出有相同年龄的年龄组

#方法二:

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;

#group_concat 将group by产生的同一个分组中的值连接起来，返回一个字符串结果。