u011652364
小段大帅
2017-09-12 08:14
采纳率: 17.4%
浏览 838

求指导两句sql怎么写?

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

6条回答 默认 最新

  • qq_23292875
    Hefei19881002 2017-09-12 08:23
    已采纳
    SELECT id,MAX(score),name FROM t GROUP BY name
    
    SELECT COUNT(id),name FROM t GROUP BY name
    
    点赞 评论
  • Neilsky
    na.nashi 2017-09-12 08: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
    ;

    点赞 评论
  • wangmusen79
    枝上小机灵 2017-09-12 08: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
    天不歪 2017-09-12 08: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 08:37

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

    点赞 评论
  • qq_37726824
    话唔多先森 2017-09-12 09: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

    点赞 评论

相关推荐