我有张A表，按条件汇总
我有张A表，按条件汇总
ID F2 F3 F4 F5
1 A03 A 5 XX
2 A02 B 3 XX
3 A03 B 2 XX
4 A04 C 5 XX
5 A02 B 6 XX
要求
按F2与F3汇总F4
条件
当F3=A 时 Sum(F4) <10 Group by F2,F3 小时，就给个相差数(sum(F4)与条件值的)
当F3=B 时 Sum(F4) <20 Group by F2,F3 ...................
当F3=C 时 sum(F4) <15 Group by F2,F3 ...................
怎么写SQL语句？
[解决办法]
select F2,F3,sum(F4),10-sum(F4) as cahzhi from A group by F2,F3 having sum(F4) <10 and F3= 'A '
select F2,F3,sum(F4),20-sum(F4) as cahzhi from A group by F2,F3 having sum(F4) <10 and F3= 'B '
select F2,F3,sum(F4),15-sum(F4) as cahzhi from A group by F2,F3 having sum(F4) <10 and F3= 'C '
[解决办法]
/*
是这样吗？
*/
declare @Test table (ID int, F2 varchar(3), F3 varchar(1), F4 int, F5 varchar(2))
insert @Test
select '1 ', 'A03 ', 'A ', '5 ', 'XX ' union all
select '1 ', 'A03 ', 'A ', '5 ', 'XX ' union all
select '1 ', 'A03 ', 'A ', '5 ', 'XX ' union all
select '2 ', 'A02 ', 'B ', '3 ', 'XX ' union all
select '3 ', 'A03 ', 'B ', '2 ', 'XX ' union all
select '4 ', 'A04 ', 'C ', '5 ', 'XX ' union all
select '5 ', 'A02 ', 'B ', '6 ', 'XX '
select F2, F3, sum(F4) - case F3
when 'A ' then case when sum(F4) < 10 then 10 else 0 end
when 'B ' then case when sum(F4) < 20 then 20 else 0 end
else case when sum(F4) < 15 then 15 else 0 end
end
from @Test group by F2, F3
[解决办法]
ID F2 F3 F4 F5
1 A03 A 5 XX
2 A02 B 3 XX
3 A03 B 2 XX
4 A04 C 5 XX
5 A02 B 6 XX
--------------------------
select F2,F3,10-sum(case when F3 = 'A ' THEN F4 END) AS A,
20-SUM(case when F3 = 'B ' THEN F4 END) AS B,
15-SUM(case when F3 = 'C ' THEN F4 END) AS C
FROM T
GROUP BY F2,F3
[解决办法]
select F2,F3,
case F3 when ( 'A ' and sum(F4) <10) then sum(f4)-10
when ( 'B ' and sum(F4) <20) then sum(f4)-20
when ( 'A ' and sum(F4) <15) then sum(f4)-15
else sum(F4) end
from tablename
group by F2,F3
