mysql - MYSQL-加入3个按月分组的表，包括空月

我有3个表：T_AUDITS，T_MONTHS和T_REGIONS。

我正在尝试按月和地区分组“审计”的数量，包括当该月不存在审计时的“零”。

我的查询仅获得具有审核的月份。我无法通过0次审核来获得月份。

SELECT
 MON.MON_DESC,
 REG.REG_ID,
 COUNT(AUD.AUD_ID)
FROM
 T_MONTHS MON
 LEFT JOIN T_AUDITS AUD
   ON (
     MON.MON_ID = MONTH(AUD.AUD_START_DATE)
   )
   RIGHT JOIN T_REGIONS REG
   ON (
     REG.REG_ID = AUD.AUD_REGION
   )
GROUP BY    MON.MON_ID, REG.REG_ID
ORDER BY REG.REG_ID,
 MON.MON_ID

我得到：

MON_NAME  |REG_ID        |COUNT(AUD.AUD_ID) |
----------|--------------|------------------|
January   |AMERICA       |52                |
February  |AMERICA       |51                |
March     |AMERICA       |57                |
April     |AMERICA       |66                |
May       |AMERICA       |54                |
January   |ASIA          |58                |
February  |ASIA          |29                |
April     |ASIA          |71                |
May       |EUROPE        |59                |

我试图得到：

MON_NAME  |REG_ID        |COUNT(AUD.AUD_ID) |
----------|--------------|------------------|
January   |AMERICA       |52                |
February  |AMERICA       |51                |
March     |AMERICA       |57                |
April     |AMERICA       |66                |
May       |AMERICA       |54                |
January   |ASIA          |58                |
February  |ASIA          |29                |
March     |ASIA          |0                 |
April     |ASIA          |59                |
May       |ASIA          |0                 |
January   |EUROPE        |0                 |
February  |EUROPE        |0                 |
March     |EUROPE        |0                 |
April     |EUROPE        |0                 |
May       |EUROPE        |79                |

有任何想法吗？提前致谢！！！

最佳答案

尝试使用CROSS JOIN获取所有月份和地区组合，然后使用LEFT JOIN进行审核。

SELECT
 MON.MON_DESC,
 REG.REG_ID,
 IFNULL(COUNT(AUD.AUD_ID), 0) AS count
FROM T_MONTHS MON
CROSS JOIN T_REGIONS REG
LEFT JOIN T_AUDIT AUD ON MONTH(AUD.AUD_START_DATE) = MON.MON_ID AND REG.REG_ID = AUD.AUD_REGION
GROUP BY REG.REG_ID, MON.MON_ID
ORDER BY REG.REG_ID, MON.MON_ID

关于mysql - MYSQL-加入3个按月分组的表，包括空月，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41131421/