时间戳

首页 > 解决方案 > 如何将多个查询编译为一个查询

sql-server - 如何将多个查询编译为一个查询

问题描述

我有多个查询，我分别执行它们，我试图找到一种方法将它们编译成一个查询。我已经尝试过交叉应用和子查询，但我不断收到错误。

查询是

   -- PreTerm Still birth
with cte as (select distinct pa.AdmitDate,pa.AdmissionID,pa.RegCode,d.DiseaseName,d.DiseaseID,PE.NationalityID,
lead (d.DiseaseID) over (partition by pa.AdmissionID order by pd.DiseaseID)as secondDia
 from v_dbPatientAdmissions pa 
join v_dbPatientMonitoring pm on pm.IPID=pa.AdmissionID
join v_DBPatientDiseases pd on pd.MonitorID=pm.MonitorID
join v_dbDiseases d on pd.DiseaseID=d.DiseaseID
left join v_dbPatientsAdditions pe on pe.PatientID=pa.PatientID
where pd.DiseaseID in (139061,128466,128462,128463,128464,128465,128466,128467,128663,128662,128661,104860,104859,104857,1397) and pa.PatientType=2)

select COUNT(*) AS PreTerm_Still_Birth ,COUNT(case WHEN cte.NationalityID=1 THEN 1 ELSE NULL END)AS S,COUNT(case WHEN cte.NationalityID!=1 THEN 2 ELSE NULL END)AS NS --cte.AdmissionID,cte.RegCode,cte.secondDia,d.DiseaseName as SecondDiseaseName
--cte.AdmitDate,cte.DiseaseName,cte.DiseaseID
from cte
join v_dbDiseases d on d.DiseaseID=cte.secondDia 
where cte.DiseaseID  in (128466,128462,128463,128464,128465,128466,128467,128663,128662,128661,104860,104859,104857,1397
) and secondDia in (139061)

--查询 1 结束

-- FullTerm Still birth
with cte as (select distinct pa.AdmitDate,pa.AdmissionID,pa.RegCode,d.DiseaseName,d.DiseaseID,pe.NationalityID,
lead (d.DiseaseID) over (partition by pa.AdmissionID order by pd.DiseaseID)as secondDia
 from v_dbPatientAdmissions pa 
join v_dbPatientMonitoring pm on pm.IPID=pa.AdmissionID
join v_DBPatientDiseases pd on pd.MonitorID=pm.MonitorID
join v_dbDiseases d on pd.DiseaseID=d.DiseaseID
left join v_dbPatientsAdditions pe on pe.Patientid=pa.PatientID
where --pa.RegCode like '%7001099667%' 
 pd.DiseaseID not in (128466,128462,128463,128464,128465,128466,128467,128663,128662,128661,104860,104859,104857,1397)and pa.PatientType=2)

select COUNT (*) AS FullTerm_Still_Birth,COUNT(case WHEN cte.NationalityID=1 THEN 1 ELSE NULL END)AS S,COUNT(case WHEN cte.NationalityID!=1 THEN 2 ELSE NULL END)AS NS  --cte.AdmissionID,cte.RegCode,cte.secondDia,d.DiseaseName as SecondDiseaseName
--cte.AdmitDate,cte.DiseaseName,cte.DiseaseID
from cte
join v_dbDiseases d on d.DiseaseID=cte.secondDia 
where cte.DiseaseID not in (128462,128463,128464,128465,128466,128467,128663,128662,128661,104860,104859,104857,1397
) and secondDia in (139061)

--查询2结束

-- Fullterm Live birth
with cte as (select distinct pa.AdmitDate,pa.AdmissionID,pa.RegCode,d.DiseaseName,d.DiseaseID,pe.NationalityID,
lead (pa.AdmitDate) over (partition by pa.regcode order by pa.admitdate)as next_AdmitDate,pa.PatientID,p.FirstName
 from v_dbPatientAdmissions pa 
join v_dbPatientMonitoring pm on pm.IPID=pa.AdmissionID
join v_DBPatientDiseases pd on pd.MonitorID=pm.MonitorID 
join v_dbDiseases d on pd.DiseaseID=d.DiseaseID
join v_dbPatientsAdditions pe on pe.ParentPatientid=pa.PatientID
join v_dbpatients p on p.PatientID=pe.PatientID
where pd.DiseaseID in (139060)
--and pa.RegCode like '%7001195301%' 
)

select COUNT (*) AS Fullterm_Live_Birth,COUNT(case WHEN cte.NationalityID=1 THEN 1 ELSE NULL END)AS S,COUNT(case WHEN cte.NationalityID!=1 THEN 2 ELSE NULL END)AS NS  from cte

--查询 3 结束

-- Fullterm Low Birth weight
with cte as (select distinct pa.AdmitDate,pa.AdmissionID,pa.RegCode,d.DiseaseName,d.DiseaseID,pe.NationalityID,
lead (d.DiseaseID) over (partition by pa.AdmissionID order by pd.DiseaseID)as secondDia
 from v_dbPatientAdmissions pa 
join v_dbPatientMonitoring pm on pm.IPID=pa.AdmissionID
join v_DBPatientDiseases pd on pd.MonitorID=pm.MonitorID
join v_dbDiseases d on pd.DiseaseID=d.DiseaseID
join v_dbpatients pp on pp.PatientID=pa.PatientID
left join v_dbPatientsAdditions pe on pe.Patientid=pa.PatientID
where --pa.RegCode like '%7001099667%'
 pd.DiseaseID in (104846,104847,104848,104849,104850,104851,104852,104853,8103,8102,104843)
  and pa.PatientType=2

 )

select  COUNT (*) AS Fullterm_Low_Birth_Weight ,COUNT(case WHEN cte.NationalityID=1 THEN 1 ELSE NULL END)AS S,COUNT(case WHEN cte.NationalityID!=1 THEN 2 ELSE NULL END)AS NS
from cte

-- 问题 4 结束

-- Delivery_Other-Twins

with cte as (select distinct pa.AdmitDate,pa.AdmissionID,pa.RegCode,d.DiseaseName,d.DiseaseID,pe.NationalityID,
lead (d.DiseaseID) over (partition by pa.AdmissionID order by pd.DiseaseID)as secondDia
 from v_dbPatientAdmissions pa 
join v_dbPatientMonitoring pm on pm.IPID=pa.AdmissionID
join v_DBPatientDiseases pd on pd.MonitorID=pm.MonitorID
join v_dbDiseases d on pd.DiseaseID=d.DiseaseID
join v_dbpatients pp on pp.PatientID=pa.PatientID
left join v_dbPatientsAdditions pe on pe.Patientid=pa.PatientID
where pa.PatientType=2 

 )

select  COUNT (*) AS Delivery_Other ,COUNT(case WHEN cte.NationalityID=1 THEN 1 ELSE NULL END)AS S,COUNT(case WHEN cte.NationalityID!=1 THEN 2 ELSE NULL END)AS NS
from cte
where cte.DiseaseID in (11866) or (cte.DiseaseID in(128576) and cte.secondDia in (
139060) )

-- 问题 5 结束

--Delivery_CS 

with cte as (select distinct pa.AdmitDate,pa.AdmissionID,pa.RegCode,d.DiseaseName,d.DiseaseID,pe.NationalityID,
lead (d.DiseaseID) over (partition by pa.AdmissionID order by pd.DiseaseID)as secondDia
 from v_dbPatientAdmissions pa 
join v_dbPatientMonitoring pm on pm.IPID=pa.AdmissionID
join v_DBPatientDiseases pd on pd.MonitorID=pm.MonitorID
join v_dbDiseases d on pd.DiseaseID=d.DiseaseID
join v_dbpatients pp on pp.PatientID=pa.PatientID
left join v_dbPatientsAdditions pe on pe.Patientid=pa.PatientID
where
 pd.DiseaseID in (140540)
  and pa.PatientType=2

 )
 select  COUNT (*) AS Delivery_CS ,COUNT(case WHEN cte.NationalityID=1 THEN 1 ELSE NULL END)AS S,COUNT(case WHEN cte.NationalityID!=1 THEN 2 ELSE NULL END)AS NS
from cte

-- 问题 6 结束

 -- Delivery_Forceps 
with cte as (select distinct pa.AdmitDate,pa.AdmissionID,pa.RegCode,d.DiseaseName,d.DiseaseID,pe.NationalityID,
lead (d.DiseaseID) over (partition by pa.AdmissionID order by pd.DiseaseID)as secondDia
 from v_dbPatientAdmissions pa 
join v_dbPatientMonitoring pm on pm.IPID=pa.AdmissionID
join v_DBPatientDiseases pd on pd.MonitorID=pm.MonitorID
join v_dbDiseases d on pd.DiseaseID=d.DiseaseID
join v_dbpatients pp on pp.PatientID=pa.PatientID
left join v_dbPatientsAdditions pe on pe.Patientid=pa.PatientID
where
 pd.DiseaseID in (140539)
  and pa.PatientType=2

 )

select  COUNT (*) AS Delivery_Forceps ,COUNT(case WHEN cte.NationalityID=1 THEN 1 ELSE NULL END)AS S,COUNT(case WHEN cte.NationalityID!=1 THEN 2 ELSE NULL END)AS NS
from cte

--查询 7 结束

 -- Delivery_Breech

with cte as (select distinct pa.AdmitDate,pa.AdmissionID,pa.RegCode,d.DiseaseName,d.DiseaseID,pe.NationalityID,
lead (d.DiseaseID) over (partition by pa.AdmissionID order by pd.DiseaseID)as secondDia
 from v_dbPatientAdmissions pa 
join v_dbPatientMonitoring pm on pm.IPID=pa.AdmissionID
join v_DBPatientDiseases pd on pd.MonitorID=pm.MonitorID
join v_dbDiseases d on pd.DiseaseID=d.DiseaseID
join v_dbpatients pp on pp.PatientID=pa.PatientID
left join v_dbPatientsAdditions pe on pe.Patientid=pa.PatientID
where
 pd.DiseaseID in (128680,104824)
  and pa.PatientType=2

 )
 select  COUNT (*) AS Delivery_Breech ,COUNT(case WHEN cte.NationalityID=1 THEN 1 ELSE NULL END)AS S,COUNT(case WHEN cte.NationalityID!=1 THEN 2 ELSE NULL END)AS NS
from cte

--查询 8 结束

  -- Delivery_Ventouse(Vacuum Extractor)

with cte as (select distinct pa.AdmitDate,pa.AdmissionID,pa.RegCode,d.DiseaseName,d.DiseaseID,pe.NationalityID,
lead (d.DiseaseID) over (partition by pa.AdmissionID order by pd.DiseaseID)as secondDia
 from v_dbPatientAdmissions pa 
join v_dbPatientMonitoring pm on pm.IPID=pa.AdmissionID
join v_DBPatientDiseases pd on pd.MonitorID=pm.MonitorID
join v_dbDiseases d on pd.DiseaseID=d.DiseaseID
join v_dbpatients pp on pp.PatientID=pa.PatientID
left join v_dbPatientsAdditions pe on pe.Patientid=pa.PatientID
where
 pd.DiseaseID in (140539)
  and pa.PatientType=2

 )

select  COUNT (*) AS Ventouse_Vacuum_Extractor,COUNT(case WHEN cte.NationalityID=1 THEN 1 ELSE NULL END)AS S,COUNT(case WHEN cte.NationalityID!=1 THEN 2 ELSE NULL END)AS NS
from cte

--查询9结束

-- Delivery_Normal-Single-Spontanous 

with cte as (select distinct pa.AdmitDate,pa.AdmissionID,pa.RegCode,d.DiseaseName,d.DiseaseID,pe.NationalityID,
lead (d.DiseaseID) over (partition by pa.AdmissionID order by pd.DiseaseID)as secondDia
 from v_dbPatientAdmissions pa 
join v_dbPatientMonitoring pm on pm.IPID=pa.AdmissionID
join v_DBPatientDiseases pd on pd.MonitorID=pm.MonitorID
join v_dbDiseases d on pd.DiseaseID=d.DiseaseID
join v_dbpatients pp on pp.PatientID=pa.PatientID
left join v_dbPatientsAdditions pe on pe.Patientid=pa.PatientID
where
 pd.DiseaseID in (140538)
  and pa.PatientType=2

 )

select  COUNT (*) AS Delivery_Normal_SingleSpontaneousDelivery ,COUNT(case WHEN cte.NationalityID=1 THEN 1 ELSE NULL END)AS S,COUNT(case WHEN cte.NationalityID!=1 THEN 2 ELSE NULL END)AS NS
from cte

--查询 10 结束

每个都提供 3 列输出，其中包含两行，例如 在此处输入图像描述

所以我想在编译所有查询后得到以下输出（只是一个例子而不是完整的输出） 在此处输入图像描述

我希望我能够清楚地解释我的问题。

标签： sql-serversql-server-2012

解决方案

The basic pattern is to chain together your Common Table Expressions and give them distinct names, then join them at the end. All produce a single row, so a simple CROSS JOIN will do fine.

;with cteCond1Pre as (
     -- the whole query you put *inside* CTE for "PreTerm Still birth"
), cteCond1 as (
    SELECT COUNT(*) AS PreTerm_Still_Birth 
        ,COUNT(case WHEN cteCond1Pre.NationalityID=1 THEN 1 ELSE NULL END)AS S
        ,COUNT(case WHEN cteCond1Pre.NationalityID!=1 THEN 2 ELSE NULL END)AS NS 
    FROM cteCond1Pre
), cteCond2Pre as (
    --  the whole query you put in a CTE for "FullTerm Still birth"
), cteCond2 as (
    SELECT COUNT(*) AS FullTerm_Still_Birth 
        ,COUNT(case WHEN cteCond2Pre.NationalityID=1 THEN 1 ELSE NULL END)AS S
        ,COUNT(case WHEN cteCond2Pre.NationalityID!=1 THEN 2 ELSE NULL END)AS NS 
    FROM cteCond2Pre
), cteCond3Pre as (
    --  the whole query you put in a CTE for "fullterm Live birth"
), cteCond3 as (
    SELECT COUNT(*) AS Fullterm_Live_Birth 
        ,COUNT(case WHEN cteCond3Pre.NationalityID=1 THEN 1 ELSE NULL END)AS S
        ,COUNT(case WHEN cteCond3Pre.NationalityID!=1 THEN 2 ELSE NULL END)AS NS 
    FROM cteCond3Pre
) SELECT * 
FROM cteCond1 CROSS JOIN cteCond2 CROSS JOIN cteCond3

推荐阅读