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

insert into student values

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

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

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

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

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

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

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

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

創(chuàng)建一張學(xué)生表如上所示,現(xiàn)要求查詢哪些學(xué)生的年齡相同，并且列出年齡相同的學(xué)生的姓名和年齡。

#方法一:

select sname, sage

from student

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

圓括號(hào)中的子查詢先對(duì)年齡分組,然后找出有相同年齡的年齡組

#方法二:

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;

開窗函數(shù)

#方法三 此法更簡潔

select sage 年齡 ,group_concat(sname) 姓名

from student

group by sage

having count(姓名)>1;

#group_concat 將group by產(chǎn)生的同一個(gè)分組中的值連接起來，返回一個(gè)字符串結(jié)果。