mysql - 如何使用另一个表中的变量进行 UNION

Question

我有两个想要合并的 sql 命令。我已经更改了实际使用的变量，试图使其更易于解释。

我想获取所有水果和蔬菜的名称，其颜色是每个年龄等于给定值的人最喜欢的颜色。

目前，我将这些查询分开，并使用 SELECT favoriteColor FROM people WHERE Age = ? 获取人们最喜欢的颜色，然后我获取颜色与 favoriteColor 匹配的所有水果和蔬菜每个人。

我得到这样的匹配水果和蔬菜:

SELECT * FROM ((SELECT 1 as type, name FROM fruits WHERE color = ?)
  UNION ALL
               (SELECT 2 as type, name FROM vegetables WHERE color = ?)) results

我基本上想要这样的东西，但我一直无法让它工作，而且我也不想运行相同的 SELECT 查询两次:

SELECT * FROM ((SELECT 1 as type, name FROM fruits WHERE color = 
                   (SELECT favoriteColor FROM people WHERE age = ?))
  UNION ALL
               (SELECT 2 as type, name FROM vegetables WHERE color = 
                   (SELECT favoriteColor FROM people WHERE age = ?))) results

而且我不介意是否得到重复的水果和蔬菜，我需要针对我的情况的重复。

例如:如果有2个人都30岁，并且都喜欢红色，我想要所有红色的水果和蔬菜。

如果有 2 个 10 岁的人，其中一个喜欢红色，另一个也喜欢绿色，我想要所有红色和绿色的水果和蔬菜。

Answer 1

不知道为什么你认为你必须在工会中测试颜色，因为司机是人。我已经猜到了我们的输出应该是什么。

create table people(id int,name varchar(10),colour varchar(1),age int);

insert into fruits values
(1,'a','a'),(2,'b','a'),(3,'b','b'),(4,'b','c');

insert into vegetables values
(1,'t','a'),(2,'t','u'),(3,'v','v'),(4,'v','w');

insert into people values
(1,'aa','a',10),(2,'bb','b',10),(3,'cc','c',10),(4,'dd','c',11);

select p.name,p.age,p.name,s.`type`,s.name,s.colour
from people p
join
(
select 1 as type, name,colour from fruits 
union
select 2 as type, name,colour from vegetables 
) s
on s.colour = p.colour 
where p.age = 10;

+------+------+------+------+------+--------+
| name | age  | name | type | name | colour |
+------+------+------+------+------+--------+
| aa   |   10 | aa   |    2 | t    | a      |
| aa   |   10 | aa   |    1 | b    | a      |
| aa   |   10 | aa   |    1 | a    | a      |
| bb   |   10 | bb   |    1 | b    | b      |
| cc   |   10 | cc   |    1 | b    | c      |
+------+------+------+------+------+--------+
5 rows in set (0.00 sec)