如何将 CTE 结果追加到主查询输出

Question

我创建了一个 TSQL 查询，该查询从数据库中的两组表中提取。 公用表表达式中的表与主查询中的表不同。 我正在加入 MRN，需要最终结果包含两组表中的帐户。 为此，我编写了以下查询：

with cteHosp as(
select Distinct p.EncounterNumber, p.MRN, p.AdmitAge
from HospitalPatients p
inner join Eligibility e on p.MRN = e.MRN
inner join HospChgDtl c on p.pt_id = c.pt_id
inner join HospitalDiagnoses d on p.pt_id = d.pt_id
where p.AdmitAge >=12
    and d.dx_cd in ('G89.4','R52.1','R52.2','Z00.129')
)
Select Distinct  a.AccountNo,  a.dob, DATEDIFF(yy, a.dob, GETDATE()) as Age
from RHCCPTDetail c
inner join RHCAppointments a on c.ClaimID = a.ClaimID
inner join Eligibility e on c.hl7Id = e.MRN
full outer join cteHosp on e.MRN = cteHosp.MRN
where DATEDIFF(yy, a.dob, getdate()) >= 12
and left(c.PriDiag,7) in ('G89.4','R52.1','R52.2', 'Z00.129')
or (
DATEDIFF(yy, a.dob, getdate()) >= 12
and LEFT(c.DiagCode2,7) in ('G89.4','R52.1','R52.2','Z00.129')
)
or (
DATEDIFF(yy, a.dob, getdate()) >= 12
and LEFT(c.DiagCode3,7) in ('G89.4','R52.1','R52.2','Z00.129')
)
or (
DATEDIFF(yy, a.dob, getdate()) >= 12
and LEFT(c.DiagCode4,7) in ('G89.4','R52.1','R52.2','Z00.129')
)   
order by AccountNo

如何将公用表表达式和主查询的输出合并到一组结果中？

Answer 1

合并执行插入、更新或删除。 我相信你想加入CTE。 如果是这样，下面是一个示例。

请注意，cteBatch 已联接到下面的主查询。

with 
cteBatch (BatchID,BatchDate,Creator,LogID)
as
(
    select 
    BatchID
    ,dateadd(day,right(BatchID,3) -1,
        cast(cast(left(BatchID,4) as varchar(4)) 
        + '-01-01' as date)) BatchDate
    ,Creator
    ,LogID
    from tblPriceMatrixBatch b
    unpivot
    (
        LogID
        for Logs in (LogIDISI,LogIDTG,LogIDWeb)
    )u
)
Select 
0 as isCurrent
,i.InterfaceID
,i.InterfaceName
,b.BatchID
,b.BatchDate
,case when isdate(l.start) = 0 and isdate(l.[end]) = 0 then 'Scheduled' 
             when isdate(l.start) = 1 and isdate(l.[end]) = 0 then 'Running'
             when isdate(l.start) = 1 and isdate(l.[end]) = 1 and isnull(l.haserror,0) = 1  then 'Failed'
             when isdate(l.start) = 1 and isdate(l.[end]) = 1 and isnull(l.haserror,0) != 1  then 'Success' 
             else 'idunno' end as stat
,l.Start as StartTime
,l.[end] as CompleteTime
,b.Creator as Usr
from EOCSupport.dbo.Interfaces i
join EOCSupport.dbo.Logs l
on i.InterfaceID = l.InterfaceID
join cteBatch b
on b.logid = l.LogID