我有5个表tb1、tb2、tb3、tb4和tb5。
这是选择查询
SELECT c.COL1, f.COL2, SUM(a.colx1* l.clox2) AS COL3
FROM tb1 c
JOIN tb2 b ON (c.id = b.idC)
JOIN tb3 l ON (b.id = l.idB)
JOIN tb4 a ON (a.id = l.idA)
JOIN tb5 f ON (f.id = a.idF)
GROUP BY c.COL1, f.COL2
这是选择查询的结果
+-----------+-------+-----------+
| COL1 | COL2 | COL3 |
+-----------+-------+-----------+
| project1 | FAM1 | 1250.251 |
| project1 | FAM2 | 2145.325 |
| project2 | FAM1 | 2146.152 |
| project3 | FAM2 | 1248.002 |
| project4 | FAM1 | 3201.684 |
| project4 | FAM2 | 7325.981 |
| project5 | FAM1 | 4657.684 |
但如果projectx没有FAMx ，我想用这种格式显示结果
+-----------+-------+-----------+
| COL1 | COL2 | COL3 |
+-----------+-------+-----------+
| project1 | FAM1 | 1250.251 |
| project1 | FAM2 | 2145.325 |
| project2 | FAM1 | 2146.152 |
| project2 | FAM2 | NULL |
| project3 | FAM2 | NULL |
| project3 | FAM2 | 1248.002 |
| project4 | FAM1 | 3201.684 |
| project4 | FAM2 | 7325.981 |
| project5 | FAM1 | 4657.684 |
| project5 | FAM1 | NULL |
这是我用RIGHT JOIN进行的测试，它不起作用！我错过了什么？
CREATE TEMPORARY TABLE tempt AS
SELECT c.COL1, f.COL2, SUM(a.colx1* l.clox2) AS COL3
FROM tb1 c
JOIN tb2 b ON (c.id = b.idC)
JOIN tb3 l ON (b.id = l.idB)
JOIN tb4 a ON (a.id = l.idA)
JOIN tb5 f ON (f.id = a.idF)
GROUP BY c.COL1, f.COL2;
SELECT t.* FROM tempt t RIGHT JOIN tb5 f ON (f.COL2 = t.COL2)
tab5表的结构
+----+--------+------+
| id | COL1 | COL2 |
+----+--------+------+
|1 | F1 | FAM1 |
|2 | F2 | FAM2 |
+----+--------+------+
左联接或右联接本身无法在所有"project"answers"FAM"值之间创建所需的额外关系。
您需要构造交叉连接以获得所需的所有行。
SELECT c.COL1, f.COL2, t.COL3
FROM tb1 c
CROSS JOIN tb5 f
LEFT JOIN tempt t ON t.COL1=c.COL1 and t.COL2=f.COL2
如果tbl1.COL1或tbl5.COL2不是唯一的，则需要添加SELECT DISTINCT
或者，也许整件事可以归结为：
SELECT c.COL1, f.COL2, SUM(a.colx1* l.clox2) AS COL3
FROM tb1 c
CROSS JOIN tb5 f
LEFT JOIN tb2 b ON (c.id = b.idC)
LEFT JOIN tb3 l ON (b.id = l.idB)
LEFT JOIN tb4 a ON (a.id = l.idA and f.id = a.idF)
GROUP BY c.COL1, f.COL2