ORACLE中的CASE……WHEN这两种写法查询结果不一样,请问区别在哪里?A:selectsum(casewhena.cityidin(47,45,35,37,36)then1else0end)甘肃北部,sum(casewhena.cityidin(34,33,32)then1else0end)甘肃东部,sum(casewhe

Question

ORACLE中的CASE……WHEN
这两种写法查询结果不一样,请问区别在哪里?
A:
select sum(case when a.city_id in (47,45,35,37,36) then
1 else 0 end) 甘肃北部,
sum(case when a.city_id in (34,33,32) then
1 else 0 end) 甘肃东部,
sum(case when a.city_id in (39,38,41) then
1 else 0 end)甘肃南部,
sum(case when a.city_id in (30,31,43) then
1 else 0 end)甘肃中部
from agt_channel a
where a.st_id = '10A';
B:
select sum(case when a.city_id in (47,45,35,37,36) then
1 else 0 end) 甘肃北部,
sum(case when a.city_id in (34,33,32) then
2 else 0 end) 甘肃东部,
sum(case when a.city_id in (39,38,41) then
3 else 0 end)甘肃南部,
sum(case when a.city_id in (30,31,43) then
4 else 0 end)甘肃中部
from agt_channel a
where a.st_id = '10A';

Answer 1

举个例子:比如
a中 当a.city_id in (34, 33, 32) 返回的是1
b中 当a.city_id in (34, 33, 32) 时,是返回的2 你这又是个sum()函数.当然结果不一样了.