mysql - 查询合并相同列名的两个表

Question

我的 MySQL 数据库中有这些表:

买家

ID|SELLER 
----------------
0 |Paul
1 |Jean
2 |David
3 |Jack
4 |John
5 |Fred
6 |Peter

巴黎

ID|CAR
---------
0 |Toyota
1 |BMW
2 |Honda

伦敦

ID|CAR
---------
3 |Ford
4 |BMW
5 |Honda
6 |Honda

我使用以下查询:

SELECT b.id, b.seller, p.car 
FROM buyers b
JOIN paris p
ON b.id = p.id
UNION ALL
SELECT b.id, b.seller, l.car 
FROM buyers b
JOIN london l
ON g.id = l.id;

得到如下结果:

ID|SELLER |CAR 
----------------
0 |Paul   |Toyota
1 |Jean   |BMW
2 |David  |Honda
3 |Jack   |Ford
4 |John   |BMW
5 |Fred   |Honda
6 |Peter  |Honda

我想检索带有“Honda”的行作为“CAR”，我试图用“Where car = 'Honda'”附加查询但没有成功..

感谢您的帮助

Answer 1

添加

WHERE car = 'Honda';

您的查询仅指第二个查询，即 UNION ALL 之后的查询。

所以要么:

SELECT b.id, b.seller, p.car FROM buyers b JOIN paris p ON b.id = p.id
  WHERE p.car = 'Honda'
UNION ALL
SELECT b.id, b.seller, l.car FROM buyers b JOIN london l ON b.id = l.id
  WHERE p.car = 'Honda'
;

或

SELECT id, seller, car
FROM
(
  SELECT b.id, b.seller, p.car FROM buyers b JOIN paris p ON b.id = p.id
  UNION ALL
  SELECT b.id, b.seller, l.car FROM buyers b JOIN london l ON b.id = l.id
) data
WHERE car = 'Honda';