mysql - 强制 SQL 结果填充缺失值

Question

我有一张类似于下面的表格:

timeUnit    country    name    count
    1          UK      Fred     12
    1          GE      Bob      1
    1          GE      John     3
    2          UK      Fred     6
    2          GE      Bob      5
    2          GE      John     8
    3          UK      Fred     4
    3          GE      Bob      6
    3          GE      John     5
    4          UK      Fred     4
    4          GE      Bob      8
    4          GE      John     9
    4          UK      Philip   6
    5          UK      Fred     3
    5          GE      Bob      2
    5          GE      John     1
    5          UK      Philip   5
    6          UK      Fred     8
    6          GE      Bob      9
    7          UK      Fred     8
    7          GE      Bob      9
    7          GE      John     8
    7          UK      Philip   6
    8          UK      Fred     5
    8          GE      Bob      1
    8          GE      John     1

我想做的相当于:

SELECT * FROM table1 WHERE country ='UK'

但结果包括 Philip 缺失的 timeUnit 结果且计数为零，即返回:

timeUnit    country    name    count
   1           UK      Fred      12
   1           UK      Philip    0
   2           UK      Fred      6
   2           UK      Philip    0
   3           UK      Fred      4
   3           UK      Philip    0
etc...

我有一种感觉，这应该可以通过临时表或查询上的某种形式的连接来实现，但我真的很难理解它。

感谢您的帮助。

Answer 1

假设您的表中已全面包含 timeUnit、name 值，您可以使用如下查询:

SELECT t1.timeUnit, 
       COALESCE(t3.country, 'UK') AS country, 
       t2.name,  
       COALESCE(SUM(count), 0) AS count
FROM (
   SELECT DISTINCT timeUnit
   FROM Table1) AS t1
CROSS JOIN (
   SELECT DISTINCT name
   FROM Table1
   WHERE country = 'UK') AS t2
LEFT JOIN (
   SELECT timeUnit, country, name, count
   FROM Table1
   WHERE country = 'UK'
) AS t3 ON t1.timeUnit = t3.timeUnit AND t2.name = t3.name
GROUP BY t1.timeUnit, t3.country, t2.name
ORDER BY t1.timeUnit, t2.name

CROSS JOIN 用于创建一个内联表，其中包含所有可能的 (timeUnit, name) 对，其中 国家=“英国”。对此表执行 LEFT JOIN 会给出一个结果集，其中包含所有可能的 (timeUnit, name) 对以及相应的计数。

Demo here