mysql - UNION 许多表，并且还包括空结果

Question

似乎这个有很多“接近的答案”，但没有什么能完全符合我的问题。

SELECT 
 'table1' AS table,
  table1.id, 
  pivot1.field1, 
  pivot2.field2, 
  pivot3.field3
FROM table1
LEFT JOIN pivot1 ON (...)
LEFT JOIN pivot2 ON (...)
LEFT JOIN pivot3 ON (...)
WHERE (...)

UNION ALL

SELECT 
 'table2' AS table,
  table2.id, 
  pivot1.field1, 
  pivot2.field2, 
  pivot3.field3
FROM table2
LEFT JOIN pivot1 ON (...)
LEFT JOIN pivot2 ON (...)
LEFT JOIN pivot3 ON (...)
WHERE (...)

UNION ALL

SELECT 
 'table3' AS table,
  table3.id, 
  pivot1.field1, 
  pivot2.field2, 
  pivot3.field3
FROM table2
LEFT JOIN pivot1 ON (...)
LEFT JOIN pivot2 ON (...)
LEFT JOIN pivot3 ON (...)
WHERE (...)

ORDER BY (...)

这适用于仅从所有表中获取匹配结果。

例子

table1|1|val|val|val
table1|2|val|val|val
table1|3|val|val|val
table2|1|val|val|val

SQLFiddle

但是，我还想为没有结果的表添加一行。

如果我尝试像这样添加一个计数列，我发现每个表的结果都被分组：

SELECT 
  COUNT(table1.id) AS matches,
 'table1' AS table,
  table1.id, 
  pivot1.field1, 
  pivot2.field2, 
  pivot3.field3
FROM table1
LEFT JOIN pivot1 ON (...)
LEFT JOIN pivot2 ON (...)
LEFT JOIN pivot3 ON (...)
WHERE (...)

UNION ALL

SELECT 
  COUNT(table2.id) AS matches,
 'table2' AS table,
  table2.id, 
  pivot1.field1, 
  pivot2.field2, 
  pivot3.field3
FROM table2
LEFT JOIN pivot1 ON (...)
LEFT JOIN pivot2 ON (...)
LEFT JOIN pivot3 ON (...)
WHERE (...)

UNION ALL

SELECT 
  COUNT(table3.id) AS matches,
 'table3' AS table,
  table3.id, 
  pivot1.field1, 
  pivot2.field2, 
  pivot3.field3
FROM table2
LEFT JOIN pivot1 ON (...)
LEFT JOIN pivot2 ON (...)
LEFT JOIN pivot3 ON (...)
WHERE (...)

ORDER BY (...)

例子

3|table1|1   |val |val |val
1|table2|1   |val |val |val
0|table3|NULL|NULL|NULL|NULL

SQLFiddle

您如何建议我实现这两个结果，如下所示：

3|table1|1   |val |val |val
3|table1|2   |val |val |val
3|table1|3   |val |val |val
1|table2|1   |val |val |val
0|table3|NULL|NULL|NULL|NULL

已编辑：一些人要求提供实际的 SQL，以便他们可以使用查询，所以为了您的方便，我插入了一些 SQLFiddle(s)。感谢所有试图提供帮助的人。

简化：除了 SQLFiddle(s) 之外，一些人还要求提供简化示例，所以这是给他们的：

table1     table2     table3
id|a|b|c   id|a|b|c   id|a|b|c
1 |1|0|0   1 |0|2|0   1 |3|3|0
2 |1|1|1   2 |2|0|0   2 |0|3|0
3 |1|1|0   3 |2|0|2   3 |3|0|0

SELECT COUNT(*) AS n, '1' AS t, id,a,b,c FROM table1 WHERE a=1
UNION ALL
SELECT COUNT(*) AS n, '2' AS t, id,a,b,c FROM table2 WHERE b=2
UNION ALL
SELECT COUNT(*) AS n, '3' AS t, id,a,b,c FROM table3 WHERE c=3

此示例忽略了连接和排序，以过度简化它。我们想要的结果如下：

 n| t|  id|   a|   b|   c
-------------------------
 3| 1|   1|   1|   0|   0
 3| 1|   2|   1|   1|   1
 3| 1|   3|   1|   1|   0
 1| 2|   1|   0|   2|   0
 0| 3|NULL|NULL|NULL|NULL

但尝试添加/删除“COUNT(*) AS n”查询片段以查看结果如何变化。

标签： mysqlleft-joinunion-all

Answer 1

一种可靠的方法是像这样对每个选择进行编码：

SELECT 
  COUNT(table1.id) AS matches,
 'table1' AS table,
  table1.id, 
  pivot1.field1, 
  pivot2.field2, 
  pivot3.field3
FROM table1
LEFT JOIN pivot1 ON (...)
LEFT JOIN pivot2 ON (...)
LEFT JOIN pivot3 ON (...)
WHERE (...)

UNION ALL

SELECT 
  0 AS matches,
 'table1' AS table,
  null, 
  null, 
  null, 
  null
WHERE NOT EXISTS (
    SELECT *
    FROM table1
    WHERE (...). -- same where clause as above
)