如何将多个 SQL 查询合并为一个？

How to combine multiple SQL queries into a single one?

提问by Attilah

I have these multiple sql queries that I want to bundle together into one single query, so that I can avoid sending multiple requests to the database from my application ( I want to receive all of these data in one single shot) :

我有这些多个 sql 查询，我想将它们捆绑到一个查询中，这样我就可以避免从我的应用程序向数据库发送多个请求（我想一次性接收所有这些数据）：

1) select pin, officeNum, isVeteran from table18 where pin = 123;

2) select streetAddress, apartmentAddress, cityAddress, stateAddress from table1 where case = (select case from table18 where pin = 123);

3) select unitAddress, cityAddress, streetAddress, apartmentAddress from table5 where pin = 123;

4) select unitAddress, cityAddress, streetAddress, apartmentAddress from table55 where seqNum = 0 and rfa = (select rfa from table18 where pin = 123);

5) select unitAddress, cityAddress, streetAddress, apartmentAddress from table103 where histCode = 0 and case = (select case from table18 where pin = 123);

6) select phone, email from table715 where histSeqNum in (select max(histSeqNum from table715))
      and histCode in (select max(histCode) from table715)
      and case = (select case from table18 where pin = 123);

Here is my schema :

这是我的架构：

(Please excuse the bad design, it's from a database created 20 years ago, there is no foreign keys)

（请原谅糟糕的设计，它来自20年前创建的数据库，没有外键）

-Table18 ( pin(PK), case, officeNum, isVeteran )

-Table18 ( pin(PK), case, officeNum, isVeteran )

-Table1 (case(PK), caseOfficer, streetAddress, apartmentAddress, cityAddress, stateAddress)

-表1（案例（PK），案例官员，街道地址，公寓地址，城市地址，州地址）

-Table5 (pin(PK), streetAddress, apartmentAddress, cityAddress, stateAddress)

-Table5 (pin(PK), streetAddress, apartmentAddress, cityAddress, stateAddress)

-Table55 (rfa(CompositeKey), seqNum(CompositeKey), rfaAddress, streetAddress, apartmentAddress, cityAddress, stateAddress)

-Table55 (rfa(CompositeKey), seqNum(CompositeKey), rfaAddress, streetAddress, apartmentAddress, cityAddress, stateAddress)

-Table103 (case(CompositeKey), histCode(CompositeKey) )

-Table103 (case(CompositeKey), histCode(CompositeKey) )

-Table715 (case(CompositeKey), histSeqNum(CompositeKey), histCode(CompositeKey), phone, email )

-Table715 (case(CompositeKey), histSeqNum(CompositeKey), histCode(CompositeKey), phone, email )

回答by t-clausen.dk

You can't. All queries returns a fixed number of columns. If you still insist on retrieving your columns that way, make sure the columns datatype is the same for all your queries and combine them with unions. I don't recommand that, but it is possible.

你不能。所有查询都返回固定数量的列。如果您仍然坚持以这种方式检索列，请确保所有查询的列数据类型都相同，并将它们与联合组合。我不建议这样做，但这是可能的。

回答by Randy

here's a set that can be unioned... (3,4,5)

这是一个可以合并的集合... (3,4,5)

select unitAddress, cityAddress, streetAddress, apartmentAddress 
from table5 where pin = 123
union
select unitAddress, cityAddress, streetAddress, apartmentAddress 
from table55 where seqNum = 0 and rfa = (select rfa from table18 where pin = 123)
union
select unitAddress, cityAddress, streetAddress, apartmentAddress 
from table103 where histCode = 0 and case = (select case from table18 where pin = 123);

you could put (2) on there also if you don't mind blank states on these...

如果你不介意这些空白状态，你也可以把 (2) 放在那里......

for example, in 2 write something like:

例如，在 2 中写下类似的内容：

select null unitAddress, streetAddress, apartmentAddress, cityAddress, stateAddress 
from table1 
where case = (select case from table18 where pin = 123);
union
select unitAddress, cityAddress, streetAddress, apartmentAddress, null
from table5 where pin = 123
union
select unitAddress, cityAddress, streetAddress, apartmentAddress, null 
from table55 where seqNum = 0 and rfa = (select rfa from table18 where pin = 123)
union
select unitAddress, cityAddress, streetAddress, apartmentAddress, null
from table103 where histCode = 0 and case = (select case from table18 where pin = 123);

also consider restructuring your nested selects to joins - similar to this: (I bet optimizing these queries will be the performance difference you are looking for)

还可以考虑将您的嵌套选择重组为连接 - 与此类似：（我敢打赌优化这些查询将是您正在寻找的性能差异）

select streetAddress, apartmentAddress, cityAddress, stateAddress 
from table1  t1, table18 t18
where t1.case = t18.case 
and t18.123;

then make sure t18 has an index on pin, and t1 has an index on case.

然后确保 t18 在 pin 上有一个索引，t1 在 case 上有一个索引。