不用group by
select *
from tbl aa
where id=(select min(id) from tbl where class=aa.class)
or id=(select max(id) from tbl where class=aa.class)
select *
from tbl aa
where id=(select min(id) from tbl where class=aa.class)
or id=(select max(id) from tbl where class=aa.class)
(select max(id) from #t group by class union select min(id) from #t group by class)
order by class
where id in(
select max(id) from 表 group by class
union all
select min(id) from 表 group by class
)
select a.*
from 表 a join 表 b
on a.class = b.class
group by a.id,a.name,a.class
having (count(case when a.id >= b.id then 1 end) = 1) or (count(case when a.id >= b.id then 1 end) = count(*))
declare @ table(id int identity,class int)insert @
select 1
union all
select 1
union all
select 1
union all
select 2
union all
select 2
union all
select 2
union all
select 3
union all
select 3
union all
select 3select a.* from @ a join @ b
on a.class = b.class
group by a.id,a.class
having (count(case when a.id >= b.id then 1 end) = 1) or (count(case when a.id >= b.id then 1 end) = count(*))/*
id class
----------- -----------
1 1
3 1
4 2
6 2
7 3
9 3(所影响的行数为 6 行)
*/
(select min(id) from test group by class)
union all
select * from test where id in
(select max(id) from test group by class)
order by class asc
-------------------------
1 xz 1
3 xl 1
4 lz 2
6 lw 2
9 dw 3
7 dz 3
我需要在结果中显示出Name
还要用group by
select a.*
from 表 a join 表 b
on a.class = b.class
group by a.id,a.name,a.class
having (count(case when a.id >= b.id then 1 end) = 1) or (count(case when a.id >= b.id then 1 end) = count(*))