我有一个表table1 三个字段a,b,c
数据如下:
a b c
0.21 1 2
0.22 2 2
0.23 3 2
0.24 4 2
0.41 1 2
0.42 2 2
0.43 3 2
0.44 4 2
我想写一个Select语句实现以下的表i1 i2 i3
<0.3 <2 求和
<0.3 >2 求和
>0.3 <2 求和
>0.3 >2 求和怎么写?谢谢
数据如下:
a b c
0.21 1 2
0.22 2 2
0.23 3 2
0.24 4 2
0.41 1 2
0.42 2 2
0.43 3 2
0.44 4 2
我想写一个Select语句实现以下的表i1 i2 i3
<0.3 <2 求和
<0.3 >2 求和
>0.3 <2 求和
>0.3 >2 求和怎么写?谢谢
select u.astr,u.bstr,sum(c) from
(select case when a<0.3 then
0
else 1
end aorder,
case when a<0.3 then
'<0.3'
else '>0.3'
end astr,
case when b<2 then
0
else 1
end border,
case when b<2 then
'<2'
else '>2'
end bstr,
c
from table1
order by aorder,border) u
group by u.aorder,u.astr,u.border,u.bstr
我自己试了
1 <0.3 <2 2
2 >0.3 >2 6
3 <0.3 >2 6
4 >0.3 <2 2
order by u.aorder
就和你的一样了
union
select '<0.3' i1, '>2' i2, sum(c) i3 from table1 where a < 0.3 and b > 2
union
select '>0.3' i1, '<2' i2, sum(c) i3 from table1 where a > 0.3 and b < 2
union
select '>0.3' i1, '>2' i2, sum(c) i3 from table1 where a > 0.3 and b > 2;
FROM (SELECT CASE
WHEN T.A < 0.3 AND T.B < 2 THEN
'a'
WHEN T.A < 0.3 AND T.B > 2 THEN
'b'
WHEN T.A > 0.3 AND T.B < 2 THEN
'c'
WHEN T.A > 0.3 AND T.B > 2 THEN
'd'
END DANGCI,
T.C
FROM TAB T) T
GROUP BY T.DANGCI