MySql 查询以获取在每个国家/地区为每个目的花费的天数？ (获取第一个表中存在的第二个表中的所有记录的计数)

Question

我有三个表 tl_log、tl_geo_countries、tl_purpose。我正在尝试为表“tl_purpose”中的每个目的获取表“tl_log”中每个国家/地区花费的天数。我尝试了下面的 mysql 查询

SELECT t.country_id AS countryID,t.reason_id AS reasonID,count(t.reason_id) AS 
days,c.name AS country, p.purpose AS purpose
FROM `tl_log` AS t
LEFT JOIN tl_geo_countries AS c ON t.country_id=c.id
LEFT JOIN tl_purpose AS p ON t.reason_id=p.id
GROUP BY t.reason_id,t.country_id ORDER BY days DESC

但是着陆了。

我无法在表“tl_log”中不存在的“tl_log”中获取每个国家/地区的目的计数。任何帮助是极大的赞赏。另外，如果问题难以理解，请告诉我。

预期输出:

下面是这三个表的结构

tl_log

tl_geo_countries

tl_purpose

Answer 1

如果您想要所有可能的国家和目的组合，即使是那些没有出现在日志表中的(这些将显示为 0)，您可以首先对两个表进行笛卡尔乘积(a CROSS join) 然后LEFT join 到日志表:

SELECT 
    c.id AS countryID,
    p.id AS reasonID,
    COUNT(t.reason_id) AS days,
    c.name AS country, 
    p.purpose AS purpose
FROM 
    tl_geo_countries AS c 
  CROSS JOIN
    tl_purpose AS p 
  LEFT JOIN
    tl_log AS t
      ON  t.country_id = c.id
      AND t.reason_id = p.id
GROUP BY 
    p.id,
    c.id 
ORDER BY 
    days DESC ;

如果您只想要日志表中存在的国家/地区的记录(但仍然是所有可能的原因/目的)，则需要稍作修改:

SELECT 
    c.id AS countryID,
    p.id AS reasonID,
    COUNT(t.reason_id) AS days,
    c.name AS country, 
    p.purpose AS purpose
FROM 
    ( SELECT DISTINCT
          country_id
      FROM 
          tl_log 
    ) AS dc  
  JOIN
    tl_geo_countries AS c
      ON c.id = dc.country_id
  CROSS JOIN
    tl_purpose AS p 
  LEFT JOIN
    tl_log AS t
      ON  t.country_id = c.id
      AND t.reason_id = p.id
GROUP BY 
    p.id,
    c.id 
ORDER BY 
    days DESC ;