mysql - 如何重复值、计数和分组？

Question

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

我的表:

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

|policy_business_units|
   |id|   |name|  |comercial_area_id|
     1     LIFE         1 
     2     ROB          1
     3     SECURE       1
     4     ACCIDENT     1

|comercial_areas|
   |id|   |name|
    1      BANK
    2      HOSPITAL

|cia_ensures|
   |id|   |name|
    1      SPRINT
    2      APPLE

信息如下:

http://sqlfiddle.com/#!2/4750f/2

我正在尝试获取 states = 0,1,2 anc 计数，如果它不存在或显示 0

Select  
  pb.name as BUSINESS_UNITS,
  ce.name as CIA,ca.name as COMERCIAL_AREAS,
  if (p.state = 0, count(p.state), 0) as state_0,
  if (p.state = 1, count(p.state), 0) as state_1,
  if (p.state = 2, count(p.state), 0) as state_2
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 ca.id=1
group by pb.id

我得到这个结果:

BUSINESS_UNITS      CIA      COMERCIAL_AREAS    STATE_0   STATE_1   STATUS_2
 LIFE            SPRINT       BANK                  1       0           0
 ROB             SPRINT       BANK                  1       0           0
 SECURE          APPLE        BANK                  1       0           0
 ACCIDENT        APPLE        BANK                  0       1           0

这是我的问题:

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

BUSINESS_UNITS      CIA      COMERCIAL_AREAS    STATE_0   STATE_1   STATUS_2
 LIFE            SPRINT       BANK                  1       0           0
 ROB             SPRINT       BANK                  1       0           0
 SECURE          SPRINT       BANK                  0       0           0
 ACCIDENT        SPRINT       BANK                  0       0           0
 LIFE            APPLE        BANK                  0       0           0
 ROB             APPLE        BANK                  0       0           0
 SECURE          APPLE        BANK                  1       0           0
 ACCIDENT        APPLE        BANK                  0       1           0

我试过了

SELECT 
   COALESCE(SUM(CASE WHEN p.state = 0 THEN 1 ELSE 0 END),0) AS state_0,
   COALESCE(SUM(CASE WHEN p.state = 1 THEN 1 ELSE 0 END),0) AS state_1,
   COALESCE(SUM(CASE WHEN p.state = 2 THEN 1 ELSE 0 END),0) AS state_2,
   count(*) AS total, p.policy_business_unit_id as units,
   p.cia_ensure_id AS cias, p.state as status
   FROM policies p
   WHERE policy_business_unit_id IN (1)
   AND cia_ensure_id IN (1,2)
   GROUP BY cia_ensure_id

有人知道怎么做吗？

拜托，我将不胜感激所有类型的帮助。谢谢。

Answer 1

您必须使用子查询，然后应用 LEFT OUTER JOIN 将它们连接在一起。顶部的 IFNULL 部分确保将不存在的状态字段中填充的 NULL 值(由外部连接产生)转换为 0。

SET @id = 1;

SELECT sections.BUSINESS_UNITS, sections.CIA, sections.AREAS,
    IFNULL(states.state_0, 0) AS STATE_0,
    IFNULL(states.state_1, 0) AS STATE_1,
    IFNULL(states.state_2, 0) AS STATE_2
    FROM (
        SELECT
            pb.id AS bus_id, pb.name AS BUSINESS_UNITS,
            ce.id AS cia_id, ce.name AS CIA,
            ca.name AS AREAS
            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
            WHERE ca.id = @id
    ) AS sections
    LEFT OUTER JOIN (
        SELECT
            pb.id AS bus_id,
            ce.id AS cia_id,
            if (p.state = 0, 1, 0) AS state_0,
            if (p.state = 1, 1, 0) AS state_1,
            if (p.state = 2, 1, 0) AS state_2
            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 ca.id = @id
    ) AS states
    ON sections.bus_id = states.bus_id
    AND sections.cia_id = states.cia_id
    ORDER BY sections.cia_id;

演示@ SQL Fiddle