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

[Hello muzi90]

比如：现在表中有3个字段：id,score,name,，根据name分组然后查询出name同一个组中的最大值和最小值(同时查询出)，用一条sql语句(比如mysql数据库)。有好的方法么？求指教。

[码无边]

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

自己整合下吧

[Hello muzi90]

引用 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

自己整合下吧

恩嫩，试了一下，都是重复的记录；

[道玄希言]

SELECT name, MAX(score) as maxs, MIN(score) as mins FROM tbl01 GROUP BY name

[LongRui888]

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)

[Hello muzi90]

比如：
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 陈欧