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

小编典典

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

sql

我有多个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);

这是我的架构：

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

-Table18（PIN（PK），case，officeNum，isVeteran）

-Table1（case（PK），caseOfficer，streetAddress，apartmentAddress，cityAddress，stateAddress）

-Table5（pin（PK），streetAddress，dutchaddress，cityAddress，stateAddress）

-Table55（rfa（CompositeKey），seqNum（CompositeKey），rfaAddress，streetAddress，ApartmentAddress，cityAddress，stateAddress）

-Table103（case（CompositeKey），histCode（CompositeKey））

-Table715（案例（CompositeKey），histSeqNum（CompositeKey），histCode（CompositeKey），电话，电子邮件）

阅读 198

2021-04-15

共1个答案

小编典典

这是一个可以合并的集合…（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);

如果您不介意这些文字上的空白状态，也可以在其中放置（2）…

例如，在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);

还考虑重组嵌套选择以联接-类似于此：（我敢打赌，优化这些查询将是您要寻找的性能差异）

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

然后确保t18在引脚上有一个索引，而t1在外壳上有一个索引。

2021-04-15