mysql - 查询以将虚拟数据放入缺失的行中

Question

我有一个缺少行的 MYSQL 表，并且想要一个查询，以便每周“GROUP”具有相同的行数。

在此示例中，我需要每周有 3 天，并且我希望每周输出中具有相同的天数/行数以进行进一步处理:

week,day,value
1,1,101
1,2,102
1,3,103
2,1,201
2,2,202
3,1,301
3,2,302

我想生成这样的东西 - 每个“周”都有相同的行数。

week,day,value
1,1,101
1,2,102
1,3,103
2,1,201
2,2,202
2,3,NULL
3,1,301
3,2,302
3,3,NULL

用零或 NULL 或某些默认值填充“值” - 即使缺失的日期设置为 NULL 或可以执行某些操作。行数/天数始终相同(本例中为 3 行)，但任何一周都可能缺少 1 天或更多天。

我有点困惑如何做到这一点。LEFT JOIN 到包含三行 1、2、3 的表是否可行？

真实的表相当大，手动创建包含所有(周、日)的交叉引用表是不切实际的。

谢谢!

创建声明

CREATE TABLE `new1` (
  `week` int(11) DEFAULT NULL,
  `day` int(11) DEFAULT NULL,
  `value` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

插入语句

INSERT INTO test.new1 
(week, day, value) 
VALUES 
(1,1,101),
(1,2,102),
(1,3,103),
(2, 1, 201),
( 2,2, 202),
( 3, 1, 301),
( 3, 2, 302);

Answer 1

您可以创建所有可能的周/日组合的列表，然后左连接现有表以提取值:

SELECT a.*,b.value
FROM (SELECT *
      FROM (SELECT DISTINCT week FROM Table1) wk
          ,(SELECT DISTINCT day FROM Table1) dy
     )a
LEFT JOIN Table1 b
  ON a.week = b.`week`
  AND a.day = b.`day`

演示:SQL Fiddle

仅添加填充行:

INSERT INTO Table1 (`week`, `day`, `value`)
    SELECT a.*,b.value
    FROM (SELECT *
          FROM (SELECT DISTINCT week FROM Table1) wk
              ,(SELECT DISTINCT day FROM Table1) dy
         )a
    LEFT JOIN Table1 b
      ON a.week = b.`week`
      AND a.day = b.`day`
    WHERE b.week IS NULL