问个问题,关于分组后查询某个字段的最大值的最小值(用一条sql语句)

muzi1314_ 2015-10-13 12:11:33
比如:现在表中有3个字段:id,score,name,,根据name分组然后查询出name同一个组中的最大值和最小值(同时查询出),用一条sql语句(比如mysql数据库)。有好的方法么?求指教。
...全文
438 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
muzi1314_ 2015-10-13
  • 打赏
  • 举报
回复
比如:
1 98 陈丽
2 87 陈丽
3 77 李扬
4 66 陈丽
5 55 陈丽
6 79 李扬
7 57 李扬
8 66 李扬
9 88 陈欧
10 44 陈欧
11 77 陈欧

输出结果:
1 98 陈丽
5 55 陈丽
6 79 李扬
7 57 李扬
9 88 陈欧
10 44 陈欧
  • 打赏
  • 举报
回复
select tb.*
from
(
select name,
max(score) as max_score,
min(score) as min_score
from tb
group name
)t
inner join tb
on tb.name = t.name and (tb.score = t.max_score or tb.score = t.min_score)
道玄希言 2015-10-13
  • 打赏
  • 举报
回复
SELECT name, MAX(score) as maxs, MIN(score) as mins FROM tbl01 GROUP BY name


muzi1314_ 2015-10-13
  • 打赏
  • 举报
回复
引用 2 楼 zy205817 的回复:

select * from (
select * from (select id,score,name from tb group by name order by score desc )

union all

select * from (select id,score,name from tb group by name order by score)
) as t
order by t.name

自己整合下吧


引用 2 楼 zy205817 的回复:

select * from (
select * from (select id,score,name from tb group by name order by score desc )

union all

select * from (select id,score,name from tb group by name order by score)
) as t
order by t.name

自己整合下吧


引用 2 楼 zy205817 的回复:

select * from (
select * from (select id,score,name from tb group by name order by score desc )

union all

select * from (select id,score,name from tb group by name order by score)
) as t
order by t.name

自己整合下吧


恩嫩,试了一下,都是重复的记录;
码无边 2015-10-13
  • 打赏
  • 举报
回复

select * from (
select * from (select id,score,name from tb group by name order by score desc )

union all

select * from (select id,score,name from tb group by name order by score)
) as t
order by t.name

自己整合下吧

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧