SQL 如何将 UNION 查询视为子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5240050/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I treat a UNION query as a sub query
提问by Michael Rutherfurd
I have a set of tables that are logically one table split into pieces for performance reasons. I need to write a query that effectively joins all the tables together so I use a single where clause of the result. I have successfully used a UNION on the result of using the WHERE clause on each subtable explicitly as in the following
我有一组表，出于性能原因，逻辑上将一个表分成几部分。我需要编写一个有效地将所有表连接在一起的查询，因此我使用结果的单个 where 子句。我已经成功地在每个子表上显式使用 WHERE 子句的结果上使用了 UNION，如下所示
SELECT * FROM FRED_1 WHERE CHARLIE = 42
UNION
SELECT * FROM FRED_2 WHERE CHARLIE = 42
UNION
SELECT * FROM FRED_3 WHERE CHARLIE = 42
but as there are ten separate subtables updating the WHERE clause each time is a pain. What I want is something like this
但由于有十个单独的子表，每次更新 WHERE 子句很痛苦。我想要的是这样的
SELECT *
FROM (
SELECT * FROM FRED_1
UNION
SELECT * FROM FRED_2
UNION
SELECT * FROM FRED_3)
WHERE CHARLIE = 42
If it makes a difference the query needs to run against a DB2 database.
如果它有所不同，则需要针对 DB2 数据库运行查询。
Here is a more comprehensive (sanitised) version of what I need to do.
这是我需要做的更全面（消毒）的版本。
select *
from ( select * from FRD_1 union select * from FRD_2 union select * from FRD_3 ) as FRD,
( select * from REQ_1 union select * from REQ_2 union select * from REQ_3 ) as REQ,
( select * from RES_1 union select * from RES_2 union select * from RES_3 ) as RES
where FRD.KEY1 = 123456
and FRD.KEY1 = REQ.KEY1
and FRD.KEY1 = RES.KEY1
and REQ.KEY2 = RES.KEY2
NEW INFORMATION:
新讯息：
It looks like the problem has more to do with the number of fields in the union than anything else. If I greatly restrict the fields I can get most of the syntax variations below working. Unfortunately, restricting the fields so much means the resulting query, while potentially useful, is not giving me the result I wanted. I've managed to get an additional 3 fields from one of the tables in addition to the 2 keys. Any more than that and the query fails.
看起来问题更多地与联合中的字段数量有关，而不是其他任何事情。如果我极大地限制字段，我可以获得下面的大部分语法变体。不幸的是，如此限制字段意味着结果查询虽然可能有用，但并没有给我想要的结果。除了 2 个键之外，我还设法从其中一个表中获得了另外 3 个字段。再多，查询就会失败。
回答by Infotekka
I believe you have to give a name to your subquery result. I don't know db2 so I'm taking a shot in the dark, but I know this works on several other platforms.
我相信您必须为子查询结果命名。我不知道 db2，所以我在黑暗中试一试，但我知道这适用于其他几个平台。
SELECT *
FROM (
SELECT * FROM FRED_1
UNION
SELECT * FROM FRED_2
UNION
SELECT * FROM FRED_3) AS T1
WHERE CHARLIE = 42
回答by Dave Barker
If the logical implementation is a single table but the physical implementation is multiple tables then how about creating a view that defines the logical model.
如果逻辑实现是单个表，而物理实现是多个表，那么如何创建定义逻辑模型的视图。
CREATE VIEW VW_FRED AS
SELECT * FROM FRED_1
UNION
SELECT * FROM FRED_2
UNION
SELECT * FROM FRED_3
then it's a simple matter of
那么这是一个简单的问题
SELECT * FROM VW_FRED WHERE CHARLIE = 42
Again, I'm not familiar with db2 syntax but this gives you the general idea.
同样，我不熟悉 db2 语法，但这为您提供了总体思路。
回答by Peter Miehle
with
FRD as ( select * from FRD_1 union select * from FRD_2 union select * from FRD_3 ),
REQ as ( select * from REQ_1 union select * from REQ_2 union select * from REQ_3 ),
RES as ( select * from RES_1 union select * from RES_2 union select * from RES_3 )
SELECT * from FRD, REQ, RES
WHERE FRD.KEY1 = 123456
and FRD.KEY1 = REQ.KEY1
and FRD.KEY1 = RES.KEY1
and REQ.KEY2 = RES.KEY2
回答by Dave Barker
I'm not familiar with DB2 syntax but why aren't you doing this as an INNER JOINor LEFT JOIN?
我不熟悉 DB2 语法，但您为什么不将其作为INNER JOIN或LEFT JOIN？
SELECT *
FROM FRED_1
INNER JOIN FRED_2
ON FRED_1.Charlie = FRED_2.Charlie
INNER JOIN FRED_3
ON FRED_1.Charlie = FRED_3.Charlie
WHERE FRED_1.Charlie = 42
If the values don't exist in FRED_2or FRED_3then use a LEFT/OUTER JOIN. I'm assuming that FRED_1is a master table, and if a record exists then it will be in this table.
如果FRED_2或FRED_3 中不存在这些值，则使用LEFT/ OUTER JOIN。我假设FRED_1是一个主表，如果记录存在，那么它将在这个表中。
回答by Leslie
maybe:
也许：
SELECT * FROM
(select * from FRD_1
union
select * from FRD_2
union
select * from FRD_3) FRD
INNER JOIN (select * from REQ_1 union select * from REQ_2 union select * from REQ_3) REQ
on FRD.KEY1 = REQ.KEY1
INNER JOIN (select * from RES_1 union select * from RES_2 union select * from RES_3) RES
on FRD.KEY1 = RES.KEY1
WHERE FRD.KEY1 = 123456 and REQ.KEY2 = RES.KEY2
