case when 语句 为什么第一句无论正确与否 都会跟随第二语句判断

数据库：MySQL
表结构：

img

img

img


我的代码：
select month(xl.zTime) '月份',
(case when((select sum(cp.pricexl.SALE_AMOUNT)from 销量明细 xl
left join 客户明细 kh on xl.customId = kh.customId
left join 产品明细 cp on xl.pid = cp.pid
where kh.Country = 'China' and month(xl.zTime) = 08) > 10000) then '合格'
when ((select sum(cp.pricexl.SALE_AMOUNT)from 销量明细 xl
left join 客户明细 kh on xl.customId = kh.customId
left join 产品明细 cp on xl.pid = cp.pid
where kh.Country = 'China' and month(xl.zTime) = 09) > 12000)then '合格'
else '不合格' end)
as '中国业绩情况',
(case when((select sum(cp.pricexl.SALE_AMOUNT)from 销量明细 xl
left join 客户明细 kh on xl.customId = kh.customId
left join 产品明细 cp on xl.pid = cp.pid
where kh.Country = 'England' and month(xl.zTime) = 08) > 10000)then '合格'
when((select sum(cp.pricexl.SALE_AMOUNT)from 销量明细 xl
left join 客户明细 kh on xl.customId = kh.customId
left join 产品明细 cp on xl.pid = cp.pid
where kh.Country = 'England' and month(xl.zTime) = 09) > 12000)then '合格'
else '不合格' end)
as '英国业绩情况'
from 销量明细 xl
left join 客户明细 kh on xl.customId = kh.customId
left join 产品明细 cp on xl.pid = cp.pid
group by month(xl.zTime)

输出结果：

img

这sql让人血压升高。。。case when 哪有你这样用的,我按你这个逻辑改了下

select mon,
case 
when Country = 'China' and mon = 8 and total>10000 then '合格' 
when kh.Country = 'China' and mon = 9 and total>12000  then '合格' 
else '不合格' end 中国业绩情况,
case 
when Country = 'England' and mon = 8 and total>10000 then '合格' 
when Country = 'England' and mon = 9 and total>12000  then '合格' 
else '不合格' end 英国业绩情况
from (
select month(xl.zTime) mon,kh.Country,sum(cp.price*xl.SALE_AMOUNT) total
from 产品明细 cp,销量明细 xl,客户明细 kh 
where xl.pid = cp.pid 
and xl.customId = kh.customId 
and month(xl.zTime) in (8,9) 
and Country in ('England','China')
group by month(xl.zTime),kh.Country
);

你可以先把中间这个子查询弄出来,看看有没有数据

稍微改了下,以后测试数据请提供建表sql及insert语句,要不然回答问题的人还得替你去造数据测试sql

select mon,
max(case 
when kh.Country = 'China' and mon = 8 and total>10000 then '合格' 
when kh.Country = 'China' and mon = 9 and total>12000  then '合格' 
when kh.Country = 'China' and mon in (8, 9) then '不合格'
 end) 中国业绩情况,
max(case 
when kh.Country = 'England' and mon = 8 and total>10000 then '合格' 
when kh.Country = 'England' and mon = 9 and total>12000  then '合格' 
when kh.Country = 'England' and mon in (8, 9) then '不合格'
 end) 英国业绩情况
from (
select month(xl.zTime) mon,kh.Country,sum(cp.price*xl.SALE_AMOUNT) total
from 产品明细 cp,销量明细 xl,客户明细 kh 
where xl.pid = cp.pid 
and xl.customId = kh.customId 
and month(xl.zTime) in (8,9) 
and Country in ('England','China')
group by month(xl.zTime),kh.Country
) group by mon;

你这个when 条件都是一样的,他肯定都是一起执行了，我搞不懂你为什么这样写,有点太过于复杂了，这个业务就三个表而已,写的这么复杂没有必要 吧。

你可以在
select 
MONTH (t2.zTime) as '月份',
这里进行判断  case when then end as ''
from t with (nolock) 
left join
(select .......,sum(t1.price*t2.SALE_AMOUNT) as TotalPirce
from t1 with (nolokc) 
left join t2 on=t1.pid=t2.pid
where .......
group by .....
)as t3 t3.customId =t.customId

但是为什么我查出来的数据都执行两遍

img