将两个select语句连接到一个

Question

如何将这些语句连接到一起返回结果？

查询1：

select scheduleentry.scheduledate,
    patientdemographics.accountnumber,
    patientdemographics.firstname,
    patientdemographics.middleinitial,
    patientdemographics.lastname,
    patientdemographics.address1,
    patientdemographics.address2,
    patientdemographics.city,
    patientdemographics.state,
    patientdemographics.zipcode,
    patientdemographics.dateofbirth,
    patientdemographics.sex,
    patientdemographics.hometelephone,
    patientinsuranceprofiles.insurance1name,
    patientinsuranceprofiles.insurance1policynumber,
    patientinsuranceprofiles.insurance1policygroupnumber,
    patientinsuranceprofiles.insurance1guarantorfirstname as insuredfirstname,
    patientinsuranceprofiles.insurance1guarantormiddleinitial as insuredmiddleinitial,
    patientinsuranceprofiles.insurance1guarantorlastname as insuredlastname,
    patientinsuranceprofiles.insurance1guarantoraddress1 as insuredaddress1,
    patientinsuranceprofiles.insurance1guarantoraddress2 as insuredaddress2,
    patientinsuranceprofiles.insurance1guarantorcity as insuredcity,
    patientinsuranceprofiles.insurance1guarantorstate as insuredstate,
    patientinsuranceprofiles.insurance1guarantorzipcode as insuredzip,
    patientinsuranceprofiles.insurance1guarantordateofbirth as insureddob,
    patientinsuranceprofiles.insurance1guarantorsex as insuredsex,
    patientinsuranceprofiles.insurance1guarantortelephonenumber as insuredhomenumber,
    insurances.eligibilitypayornumber
from patientdemographics
inner join patientinsuranceprofiles
on  patientdemographics.accountnumber = patientinsuranceprofiles.patientaccountnumber
inner join scheduleentry
on  patientinsuranceprofiles.patientaccountnumber = scheduleentry.patientaccount
inner join insurances
on  patientinsuranceprofiles.insurance1mnemonic = insurances.mnemonic
where datediff(d, getdate(), scheduledate) = 1
    and patientinsuranceprofiles.activeflag = 1
order by scheduledate asc

查询2：

select scheduleentry.scheduledate,
    patientdemographics.accountnumber,
    patientdemographics.firstname,
    patientdemographics.middleinitial,
    patientdemographics.lastname,
    patientdemographics.address1,
    patientdemographics.address2,
    patientdemographics.city,
    patientdemographics.state,
    patientdemographics.zipcode,
    patientdemographics.dateofbirth,
    patientdemographics.sex,
    patientdemographics.hometelephone,
    patientinsuranceprofiles.insurance2name,
    patientinsuranceprofiles.insurance2policynumber,
    patientinsuranceprofiles.insurance2policygroupnumber,
    patientinsuranceprofiles.insurance2guarantorfirstname as insuredfirstname,
    patientinsuranceprofiles.insurance2guarantormiddleinitial as insuredmiddleinitial,
    patientinsuranceprofiles.insurance2guarantorlastname as insuredlastname,
    patientinsuranceprofiles.insurance2guarantoraddress1 as insuredaddress1,
    patientinsuranceprofiles.insurance2guarantoraddress2 as insuredaddress2,
    patientinsuranceprofiles.insurance2guarantorcity as insuredcity,
    patientinsuranceprofiles.insurance2guarantorstate as insuredstate,
    patientinsuranceprofiles.insurance2guarantorzipcode as insuredzip,
    patientinsuranceprofiles.insurance2guarantordateofbirth as insureddob,
    patientinsuranceprofiles.insurance2guarantorsex as insuredsex,
    patientinsuranceprofiles.insurance2guarantortelephonenumber as insuredhomenumber,
    insurances.eligibilitypayornumber
from patientdemographics
inner join patientinsuranceprofiles
on  patientdemographics.accountnumber = patientinsuranceprofiles.patientaccountnumber
inner join scheduleentry
on  patientinsuranceprofiles.patientaccountnumber = scheduleentry.patientaccount
inner join insurances
on  patientinsuranceprofiles.insurance2mnemonic = insurances.mnemonic
where datediff(d, getdate(), scheduledate) = 1
    and patientinsuranceprofiles.activeflag = 1
order by scheduledate asc

Answer 1

一种方法是像其他人所建议的那样使用UNION ALL方法。

(Query 1)
union all
(Query 2)

对于您的情况，也许大小写和对联接条件的少量修改都会奏效。请试用以下方法：

select se.scheduledate,
    pd.accountnumber,
    pd.firstname,
    pd.middleinitial,
    pd.lastname,
    pd.address1,
    pd.address2,
    pd.city,
    pd.state,
    pd.zipcode,
    pd.dateofbirth,
    pd.sex,
    pd.hometelephone,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1name                     else pip.insurance2name end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1policynumber             else pip.insurance2policynumber end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1policygroupnumber        else pip.insurance2policygroupnumber end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantorfirstname       else pip.insurance2guarantorfirstname as insuredfirstname end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantormiddleinitial   else pip.insurance2guarantormiddleinitial as insuredmiddleinitial end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantorlastname        else pip.insurance2guarantorlastname as insuredlastname end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantoraddress1        else pip.insurance2guarantoraddress1 as insuredaddress1 end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantoraddress2        else pip.insurance2guarantoraddress2 as insuredaddress2 end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantorcity            else pip.insurance2guarantorcity as insuredcity end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantorstate           else pip.insurance2guarantorstate as insuredstate end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantorzipcode         else pip.insurance2guarantorzipcode as insuredzip end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantordateofbirth     else pip.insurance2guarantordateofbirth as insureddob end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantorsex             else pip.insurance2guarantorsex as insuredsex end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantortelephonenumber else pip.insurance2guarantortelephonenumber as insuredhomenumber end,
    i.eligibilitypayornumber
from patientdemographics pd
inner join patientinsuranceprofiles pip
on  pd.accountnumber = pip.patientaccountnumber
inner join scheduleentry se
on  pip.patientaccountnumber = se.patientaccount
inner join insurances i
on  i.mnemonic in (pip.insurance1mnemonic, pip.insurance2mnemonic)
where datediff(d, getdate(), scheduledate) = 1
    and pip.activeflag = 1
order by scheduledate asc

Answer 2

您可以在两个查询之间使用UNION ALL。

SELECT ...
FROM ...
WHERE...

UNION ALL

SELECT ...
FROM ....
WHERE...

您可以在这里阅读UNION和UNION ALL的差异：What is the difference between UNION and UNION ALL?

你也可以使用CTE -如果有一个列你可以加入，例如。

;WITH Query1 AS
(
 SELECT Column1
 FROM ...
)
,Query2 AS
( 
 SELECT Column1
 FROM ...
)
SELECT     *
FROM       Query1 AS Q1
INNER JOIN Query2 AS Q2 ON Q1.Column1 = Q2.Column1