select id,max(sl1) as MaxSL,min(sl1) as MinSL, avg(sl1) as AvgSL
from
(
select id,sl1 from tablename
union all
select id,sl2 from tablename
union all
select id,sl3 from tablename
) a
group by id
from
(
select id,sl1 from tablename
union all
select id,sl2 from tablename
union all
select id,sl3 from tablename
) a
group by id
insert #t
select 10,11,13
union select 9,8,7
union select 20,21,19select * from #tselect id,max(sl1) maxSL,min(sl1) minSL,avg(sl1*1.0) avgSL
from
(
select id,sl1 from #t
union all
select id,sl2 from #t
union all
select id,sl3 from #t
) a
group by id-- orselect id,
case when (sl1>=sl2 and sl2>=sl3) or (sl1>=sl3 and sl3>=sl2) then sl1
when (sl2>=sl1 and sl1>=sl3) or (sl2>=sl3 and sl3>=sl1) then sl2
when (sl3>=sl1 and sl1>=sl2) or (sl3>=sl2 and sl2>=sl1) then sl3
end maxSL,-- 最大值
case when (sl1>=sl2 and sl2>=sl3) or (sl2>=sl1 and sl1>=sl3) then sl3
when (sl1>=sl3 and sl3>=sl2) or (sl3>=sl1 and sl1>=sl2) then sl2
when (sl2>=sl3 and sl3>=sl1) or (sl3>=sl2 and sl2>=sl1) then sl1
end minSL,-- 最小值
(sl1 + sl2 + sl3)*1.0/3 avgSL -- 平均值
from #tdrop table #t
想法不错呀!
因为我的数量字段不只3个有很多,
而且可以给用户选择哪些数量字段需要一起求max,min,avg,
这样这些数量字段都是动态的,这样这些union all都是动态的,写起来就会麻烦些了:(
不过已经可以解决了,谢谢!
但愿能有更好的方法出现,呵呵!