2 u011652364 u011652364 于 2017.09.12 16:14 提问

求指导两句sql怎么写?

数据表结构和数据如图所示:图片说明
第一个:需要得到的结果是求出每个人分数最高的信息,结果应为:
id name score
1 a 11
2 b 2
3 c 3
第二个:求每个人记录的数量,结果应为:
name count
a 2
b 1
c 1

6个回答

qq_23292875
qq_23292875   2017.09.12 16:23
已采纳
SELECT id,MAX(score),name FROM t GROUP BY name

SELECT COUNT(id),name FROM t GROUP BY name
wangmusen79
wangmusen79   2017.09.12 16:30

MySQL的写法
第一个:
SELECT id,NAME,MAX(score) score FROM tb GROUP BY NAME ORDER BY id;
第二个:
SELECT NAME,COUNT(NAME) count FROM tb GROUP BY NAME;

lihaozheng329
lihaozheng329   2017.09.12 16:31
 SELECT id,name,max(score)  FROM tbl
GROUP BY name
 SELECT name,count(name)  FROM tbl
GROUP BY name
bmxkkk
bmxkkk   2017.09.12 16:37

分数最高 select * from (select rank() over (partition by name order by score desc) as rk,t1.* from t1 ) a where a.rk = 1

Neilsky
Neilsky   2017.09.12 16:19

select t1.*
form tbl t1
where not exists (
select *
from tbl t2
where t1.name = t2.name
and t1.score < t2.score
);

select name, count(*)
from tbl
group by name
;

qq_37726824
qq_37726824   2017.09.12 17:13

select a.id,b.name,b.score 最高分数 from tt a ,(select name,max(score)as score from tt b group by b.name) b
where a.name=b.name and a.score=b.score

select name,max(score)as 最高成绩 from tt b group by b.name

Csdn user default icon
上传中...
上传图片
插入图片