# 求一分组取最小值的语句,该怎么解决

www.MyException.Cn  网友分享于：2013-03-17  浏览：43次

r1　　　　r2　　　r3
------------------------------

r1　　　r2> 2时r3的最小值　　r2 <2时r3的最小值

------解决方案--------------------
declare @t table(r1 varchar(10),r2 int,r3 int)
insert into @t select '张三 ',1,323
insert into @t select '张三 ',2,24
insert into @t select '张三 ',2,22
insert into @t select '张三 ',4,24
insert into @t select '李四 ',3,25
insert into @t select '李四 ',3,2
insert into @t select '李四 ',3,25
insert into @t select '王二 ',4,5
insert into @t select '李四 ',5,7
insert into @t select '李四 ',2,98

select
r1,
min(case when r2 <2 then r3 end),
min(case when r2> 2 then r3 end)
from
@t
group by
r1

/*
r1
---------- ----------- -----------

*/
------解决方案--------------------
create table A(r1 varchar(10), r2 int, r3 int)
insert A select '张三 ', 1, 323
union all select '张三 ', 2, 24
union all select '张三 ', 2, 22
union all select '张三 ', 4, 24
union all select '李四 ', 3, 25
union all select '李四 ', 3, 2
union all select '李四 ', 3, 25
union all select '王二 ', 4, 5
union all select '李四 ', 5, 7
union all select '李四 ', 2, 98

select r1,
[r2> 2时r3的最小值]=(select min(r3) from A where r1=tmp.r1 and r2> 2),
[r2 <2时r3的最小值]=(select min(r3) from A where r1=tmp.r1 and r2 <2)
from A tmp
group by r1

--result
r1 r2> 2时r3的最小值 r2 <2时r3的最小值
---------- ----------- -----------

(3 row(s) affected)