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

Question

数据库：MySQL
表结构：

我的代码：
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)

输出结果：

Answer 1

这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;