插入测试记录:
[sql] view plain copy
insert into students values('数学','Jack','Tianjin',80)
insert into students values('数学','Jordan','Tianjin',80)
insert into students values('数学','James','Beijing',55)
insert into students values('英语','Jack','Tianjin',90)
insert into students values('英语','Jordan','Tianjin',60)
insert into students values('英语','James','Beijing',100)
insert into students values('语文','Jack','Tianjin',60)
insert into students values('语文','Tom','Tianjin',77)
insert into students values('语文','Jordan','Tianjin',68)
想要抓取每个科目第一名的整条信息,可以使用Row_number()函数:
select *
from (
select course,stu_name,city,score,ROW_NUMBER() over(partition by course order by score desc) as rn
from students
) a
where a.rn <=1;
分组查询,查询出每组数据的数量
select count(*) from table group by xx
以上一个sql为结果集,查询出最大的数量
select max(xx) from (select count(*) from table group by xx ) a
a是取的别名
xx是你分组的表的列名
table换成表名
题目是显示选课门数最多同学的学号和选课门数
追答你的表结构是什么样子的,1张表吗,给我截个图也许啊
追问你试试,应该是可以的
追问a和b是什么意思?
a 和b 分别是两个结果集的别名
select * from
(select Sno , count(*) from sc group by Sno) a
where count = (select max(count) from (select count(*) from sc group by Sno) b)
我把你表都弄下来了,试了挺好
count 是unknown
追答我这里是没问题的,未知列可能是没加别名的问题, 那你count(*)加上别名count(*) c
报错,好像不能套用