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,"计算机系"),

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

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

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

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

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

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

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

#（1）查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。

#方法一:

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;

学习更多数据分析知识欢迎加入CDA网校会员 https://www.cda.cn/member.html?utm_source=weitao