三种情况

Question

>我在 CASE WHEN 中有 2 个条件，但结果不是我想要

的这是我的代码：

SELECT CASE WHEN count(b.id_canteen) < 2 and status = 'Active' THEN 'Available'
ELSE 'Full' END as quota, 
a.id_canteen, 
name_canteen, 
count(b.id_counter) as total 
from canteen_A a 
join canteen_B b  on a.id_canteen=b.id_canteen 
group by a.id_canteen,
name_canteen,
status;

这就是结果

=================================================
|   quota   | id_canteen | name_canteen | total |
=================================================
| Available | Canteen01   | Canteen A   |   1   |
| Available | Canteen02   | Canteen B   |   1   |
| Full      | Canteen01   | Canteen A   |   1   |
=================================================

我想要的结果是这样的

=================================================
|   quota   | id_canteen | name_canteen | total |
=================================================
| Available | Canteen01   | Canteen A   |   2   |
| Available | Canteen02   | Canteen B   |   1   |
=================================================

这是字段状态

==============================
|   Status   |  id_canteen   |
==============================
| Not Active |   Canteen01   |
|   Active   |   Canteen01   |
|   Active   |   Canteen02   |
==============================

更新： 我想在 2 <添加条件计数(状态(活动。我该怎么办？>

Answer 1

问题是GROUP BY. 你不想status里面。 所以，我认为：

select (case when count(b.id_canteen) < 2 and
sum(case when status = 'Active' then 1 else 0 end) > 0
then 'Available' 
else 'Full' 
end) as quota, 
a.id_canteen, name_canteen, 
count(b.id_counter) as total 
from canteen_A a join
canteen_B b 
on a.id_canteen = b.id_canteen 
group by a.id_canteen, name_canteen

Answer 2

这返回什么？

SELECT
X.id_canteen, 
X.name_canteen, 
CASE
WHEN X.total < 2 AND X.status = 'Active' THEN
'Available'
ELSE
'Full'
END quota
FROM
(
SELECT
a.id_canteen, 
a.name_canteen,
b.status
COUNT(b.id_counter) total 
FROM
canteen_A a 
JOIN
canteen_B b
ON a.id_canteen = b.id_canteen
GROUP BY
a.id_canteen,
a.name_canteen,
b.status
) X