sql - 在 CTE 中声明变量 SQL Server 2008

Question

我怎样才能实现类似下面的东西:

declare @myInt int
set @myInt=(select count(*) from x)
;with x as

(
select row_number() over(partition by c.patientid order by c.admissiondate) as rn
    ,c.patientid,c.admissiondate
    ,max(c.claimsfromdate)  as maxHemiDate
    ,min(c.claimsfromdate) minHemiDate  
    ,(
        select max(c2.claimsfromdate)
            from claims as c2
            where c2.patientid=c.patientid
            group by c2.patientid
     ) as maxClaimsDate

         ,p.drgCode
         ,datediff(dd,min(c.claimsfromdate),max(c.claimsfromdate)) /7 as weeksWithHemi
         from claims as c inner join icdclaims as ci on ci.id=c.id
         inner join tblicd as t on t.icd_id=ci.icd_id
         inner join patient as p on p.patientid=c.patientid
         and p.admissiondate = c.admissiondate
         and p.dischargedate = c.dischargedate
         where t.icdText like '%X%'  and p.statecode='21'
         group by c.patientid, c.admissiondate, p.drgCode
)

select p.patientid, count(*)
    from patient as p
    left join x on x.patientid=p.patientid
    where x.patientid is null
    group by p.patientid

执行时抛出的错误是

invalid object name x

我觉得这会发生，因为变量声明在 CTE 之外。如果我将声明移到 WITH 的括号内，我会收到另一个错误。

如何在 CTE 中分配这样的变量？或者您根本不能使用从 CTE 中提取数据的变量吗？

Answer 1

如果您使用的是 SQL Server 2005 或更高版本，您可以将总计放在每一行:

select t.*, cnt, count(*) over () as NumRows
from (select p.patientid, count(*) as cnt
      from patient as p left join
           x
           on x.patientid=p.patientid
      where x.patientid is null
      group by p.patientid
    ) t

如果您使用总计来计算百分比或类似的东西，将值放在每一行上可能会很方便。