我不确定该问题如何,所以让我举一个我的问题的例子。
我有:
SELECT p.id, p.name, p.info, r.name
FROM p
LEFT JOIN q ON q.p_name = p.name
LEFT JOIN r ON r.id = q.r_id AND r.type = 'bar'
GROUP BY p.id
对于桌子,假设我有
p
+--+-----+-----+
|id|name |info |
+--+-----+-----+
| 1|'ABC'|'Yes'|
| 2|'DEF'|'No' |
| 3|'GHI'|'Myb'|
| 4|'JKL'|'Yes'|
+--+-----+-----+
q
+------+----+
|p_name|r_id|
+------+----+
|'ABC' | 2 |
|'ABC' | 3 |
|'ABC' | 1 |
|'DEF' | 3 |
|'DEF' | 4 |
|'HIJ' | 2 |
|'HIJ' | 3 |
+------+----+
r
+--+-----+-----+
|id|name |type |
+--+-----+-----+
| 1|'Cya'|'bar'|
| 2|'Hi' |'grt'|
| 3|'Bye'|'foo'|
| 4|'Ltr'|'bar'|
+--+-----+-----+
我想最终得到的是:
desired output
+------+--------+--------+--------+
| p.id | p.name | p.info | r.name |
+------+--------+--------+--------+
| 1 | 'ABC' | 'Yes' | 'Cya' |
| 2 | 'DEF' | 'No' | 'Ltr' |
| 3 | 'GHI' | 'Myb' | NULL |
| 4 | 'JKL' | 'Yes' | NULL |
+------+--------+--------+--------+
当我尝试上述查询时,我得到了等效的:
wrong output
+------+--------+--------+--------+
| p.id | p.name | p.info | r.name |
+------+--------+--------+--------+
| 1 | 'ABC' | 'Yes' | NULL |
| 2 | 'DEF' | 'No' | NULL |
| 3 | 'GHI' | 'Myb' | NULL |
| 4 | 'JKL' | 'Yes' | NULL |
+------+--------+--------+--------+
当我尝试通过将" Where"子句添加到
来更改查询时SELECT p.id, p.name, p.info, r.name
FROM p
LEFT JOIN q ON q.p_name = p.name
LEFT JOIN r ON r.id = q.r_id
WHERE r.type = 'bar'
GROUP BY p.id
我得到以下不正确的输出
incorrect output
+------+--------+--------+--------+
| p.id | p.name | p.info | r.name |
+------+--------+--------+--------+
| 1 | 'ABC' | 'Yes' | 'Cya' |
| 2 | 'DEF' | 'No' | 'Ltr' |
+------+--------+--------+--------+
如何及时获得所需的输出?
编辑:如果我删除"小组",则将留下以下不正确输出:
+------+--------+--------+--------+
| p.id | p.name | p.info | r.name |
+------+--------+--------+--------+
| 1 | 'ABC' | 'Yes' | NULL |
| 1 | 'ABC' | 'Yes' | NULL |
| 1 | 'ABC' | 'Yes' | 'Cya' |
| 2 | 'DEF' | 'No' | NULL |
| 2 | 'DEF' | 'No' | 'Ltr' |
| 3 | 'GHI' | 'Myb' | NULL |
| 3 | 'GHI' | 'Myb' | NULL |
| 4 | 'JKL' | 'Yes' | NULL |
+------+--------+--------+--------+
堆叠加入,这样:
SELECT p.id, p.name, p.info, r.name
FROM p
LEFT JOIN (q LEFT JOIN r ON r.id = q.r_id AND r.type = 'bar')
ON q.p_name = p.name
GROUP BY p.id
您的查询看起来不错,但是group by
不需要您提供的数据:
SELECT p.id, p.name, p.info, r.name
FROM p LEFT JOIN
q
ON q.p_name = p.name LEFT JOIN
r
ON r.id = q.r_id AND r.type = 'bar';
GROUP BY
不应将最后一列从非NULL
转到NULL
。
我已经导入您的数据集来测试我的查询,以下内容将给出您的"所需输出":
SELECT p.id, p.name, p.info, r.name
FROM (
SELECT p.id, MAX(r.id) as max_r_id
FROM p
LEFT JOIN q ON q.p_name = p.name
LEFT JOIN r ON r.id = q.r_id AND r.type = 'bar'
GROUP BY p.id
) AS result
INNER JOIN p ON p.id = result.id
LEFT JOIN r ON r.id = result.max_r_id
这是我考虑使用正确的加入
的少数情况之一SELECT p.id, p.name, p.info, r.name
FROM r
JOIN q ON q.r_id = r.id
RIGHT JOIN p ON p.name = q.p_name AND r.type = 'bar';
如果您关心性能..
SELECT p.id, p.name, p.info, r.name
FROM p
JOIN q ON q.p_name = p.name
JOIN r ON r.id = q.r_id
WHERE r.type = 'bar'
UNION ALL
SELECT p.id, p.name, p.info, NULL
FROM p
WHERE NOT EXISTS (
SELECT *
FROM r
JOIN q ON q.r_id = r.id
WHERE q.p_name = p.name
AND r.type = 'bar'
);
http://rextester.com/omd47103