mysql - 如何在一组总和中显示 0？

Question

我正在尝试创建一个交叉表，但我需要一种通过 cia_ensures 重复每个 policy_business_unit 组的查询

我的表:

|policies|
  |id|  |client|  |policy_business_unit_id|  |cia_ensure_id|   |state|       
   1      MATT                  1                   1             0
   2      STEVE                 1                   2             1
   3      BILL                  1                   3             2
   4      LARRY                 1                   4             0

|policy_business_units|
   |id|   |name|  |comercial_area_id|
     1     LIFE         1 

|comercial_areas|
   |id|   |name|
    1      BANK

|cia_ensures|
   |id|   |name|
    1      SPRINT
    2      APPLE
    3      PEPSI
    4      ORANGE
    5      BT

信息如下:

http://sqlfiddle.com/#!2/37690/2

我正在尝试使用所有 cia_ensures 显示计算结果:

SELECT ce.name AS CIAS, pb.name AS UNIT,
   SUM(CASE WHEN p.state =0 THEN 1 ELSE 0 END ) AS ACTIVES, 
   SUM(CASE WHEN p.state =1 THEN 1 ELSE 0 END ) AS INACTIVES,
   SUM(CASE WHEN p.state =2 THEN 1 ELSE 0 END ) AS OTHERS
From policies p 
  INNER JOIN policy_business_units pb ON pb.id = p.policy_business_unit_id
  INNER JOIN comercial_areas ca ON ca.id = pb.comercial_area_id
  INNER JOIN cia_ensures ce ON ce.id = p.cia_ensure_id
WHERE policy_business_unit_id IN (1) 
GROUP BY cia_ensure_id

我得到这个结果:

UNIT             CIAS        COMERCIAL_AREAS    STATE_0   STATE_1   STATUS_2
 LIFE            SPRINT       BANK                  1       0           0
 LIFE            APPLE        BANK                  0       1           0
 LIFE            PEPSI        BANK                  0       0           1
 LIFE            ORANGE       BANK                  1       0           0

这是我的问题:我想显示所有的 cia_ensure_id，如果没有关系则显示 0

我该怎么做才能得到这个结果？

UNIT             CIAS        COMERCIAL_AREAS    STATE_0   STATE_1   STATUS_2
 LIFE            SPRINT       BANK                  1       0           0
 LIFE            APPLE        BANK                  0       1           0
 LIFE            PEPSI        BANK                  0       0           1
 LIFE            ORANGE       BANK                  1       0           0
 LIFE            BT           BANK                  0       0           0 

我试过了

SELECT ce.name AS CIAS, pb.name AS UNIT,
   SUM(CASE WHEN p.state =0 THEN 1 ELSE 0 END ) AS ACTIVES, 
   SUM(CASE WHEN p.state =1 THEN 1 ELSE 0 END ) AS INACTIVES,
   SUM(CASE WHEN p.state =2 THEN 1 ELSE 0 END ) AS OTHERS
From policies p 
  INNER JOIN policy_business_units pb ON pb.id = p.policy_business_unit_id
  INNER JOIN comercial_areas ca ON ca.id = pb.comercial_area_id
  INNER JOIN cia_ensures ce ON ce.id = p.cia_ensure_id
WHERE policy_business_unit_id IN (1) AND cia_ensured_id IN (1,2,3,4,5)
GROUP BY cia_ensure_id

将接受各种帮助。

拜托，我将不胜感激。

谢谢。

Answer 1

SELECT ce.name AS CIAS, pb.name AS UNIT,
   SUM(CASE WHEN p.state =0 THEN 1 ELSE 0 END ) AS ACTIVES, 
   SUM(CASE WHEN p.state =1 THEN 1 ELSE 0 END ) AS INACTIVES,
   SUM(CASE WHEN p.state =2 THEN 1 ELSE 0 END ) AS OTHERS
From policies p 
  INNER JOIN policy_business_units pb ON pb.id = p.policy_business_unit_id
  INNER JOIN comercial_areas ca ON ca.id = pb.comercial_area_id
  RIGHT JOIN cia_ensures ce ON ce.id = p.cia_ensure_id
WHERE (policy_business_unit_id IN (1) or policy_business_unit_id is null)
GROUP BY cia_ensure_id

从内向右更改，如果它在其他表之前，则可以向左，因为它是最后一张表。基本上，您需要来自 CIA 的所有记录，并且只需要那些在其他表中匹配的记录。

请注意，您还必须在 policy_business_Unit 上查找空值，否则 (1) 将排除由右连接添加的行。

或者将条件移动到连接中，这样它只适用于连接之前的行

SELECT ce.name AS CIAS, pb.name AS UNIT,
   SUM(CASE WHEN p.state =0 THEN 1 ELSE 0 END ) AS ACTIVES, 
   SUM(CASE WHEN p.state =1 THEN 1 ELSE 0 END ) AS INACTIVES,
   SUM(CASE WHEN p.state =2 THEN 1 ELSE 0 END ) AS OTHERS
From policies p 
  INNER JOIN policy_business_units pb 
    ON pb.id = p.policy_business_unit_id 
    and policy_business_unit_id IN (1)
  INNER JOIN comercial_areas ca ON ca.id = pb.comercial_area_id
  RIGHT JOIN cia_ensures ce ON ce.id = p.cia_ensure_id
GROUP BY cia_ensure_id

假设所有 NULL PB.names 都可以读取“LIFE”，然后将第一个选择行更改为读取...但这对我来说是一个错误的假设。因此，除非有规则对此进行管理，否则 null 应该是基于设计的有效响应。

SELECT ce.name AS CIAS, coalesce(pb.name,'LIFE') AS UNIT,