时间戳

首页 > 解决方案 > 三个条件以防万一

sql - 三个条件以防万一

问题描述

我在 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   |
==============================

更新：我想添加条件 count(status) active < 2 。我应该怎么办？

标签： sqlsql-serversql-server-2014

解决方案

问题是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

推荐阅读