特设2×2列联表的SQL Server 2008(Ad hoc 2x2 contingency ta

我期待在创建的方式偶然性表在SQL Server 2008中他们并不一定要出现在2×2矩阵的典型。我只是想知道，如果有人在那里可以看到比我更好的解决方案。

请参考图片的清晰度。红色的字母是为了简单起见平方的名字。标签X +是指X是存在于该小区和相反也是如此。

我将我的标签与查询框的字母表，它们代表

A

       select count(*) from 
    (

    select distinct p.patientid
        from Patient as p
        inner join icdpatient as picd on picd.patientid = p.patientid
        and picd.admissiondate = p.admissiondate
        and picd.dischargedate = p.dischargedate
        inner join tblicd as t on t.icd_id = picd.icd_id
        where t.icdText like '%x%' 
    ) as t
    inner join 
    (
    select distinct p.patientid
        from Patient as p
        inner join icdpatient as picd on picd.patientid = p.patientid
        and picd.admissiondate = p.admissiondate
        and picd.dischargedate = p.dischargedate
        inner join tblicd as t on t.icd_id = picd.icd_id
        where t.icdText like '%y%'
    ) as s on s.patientid=t.patientid

B
select count(*) from 
(

select distinct p.patientid
    from Patient as p
    inner join icdpatient as picd on picd.patientid = p.patientid
    and picd.admissiondate = p.admissiondate
    and picd.dischargedate = p.dischargedate
    inner join tblicd as t on t.icd_id = picd.icd_id
    where t.icdText like '%x%' 
) as t
left join 
(
select distinct p.patientid
    from Patient as p
    inner join icdpatient as picd on picd.patientid = p.patientid
    and picd.admissiondate = p.admissiondate
    and picd.dischargedate = p.dischargedate
    inner join tblicd as t on t.icd_id = picd.icd_id
    where t.icdText like '%y%'
) as s on s.patientid=t.patientid
where s.patientid is null

C
select * from 
(

select distinct p.patientid
    from Patient as p
    inner join icdpatient as picd on picd.patientid = p.patientid
    and picd.admissiondate = p.admissiondate
    and picd.dischargedate = p.dischargedate
    inner join tblicd as t on t.icd_id = picd.icd_id
    where t.icdText like '%x%' 
) as t
right join 
(
select distinct p.patientid
    from Patient as p
    inner join icdpatient as picd on picd.patientid = p.patientid
    and picd.admissiondate = p.admissiondate
    and picd.dischargedate = p.dischargedate
    inner join tblicd as t on t.icd_id = picd.icd_id
    where t.icdText like '%y%'
) as s on s.patientid=t.patientid
where t.patientid is null

d这一次我有点玄乎约，但我想我会做这样的事

declare @d int
set @d = (select count(distinct p.patientid) from Patient as p) - b -c

其目的是发现整个人口减去那些只用X和ONLY，其中y

Answer 1:

是! 还有更简单的方法。假设你的加入不会产生重复的患者：

select (case when t.icdText like '%x%' then 'X' else 'NO-X' end) as X,
       (case when t.icdText like '%y%' then 'Y' else 'NO-Y' end) as Y,
       count(*) as cnt
from Patient p inner join
     icdpatient picd
     on picd.patientid = p.patientid and
        picd.admissiondate = p.admissiondate and
        picd.dischargedate = p.dischargedate inner join
     tblicd t
     on t.icd_id = picd.icd_id
group by  (case when t.icdText like '%x%' then 'X' else 'NO-X' end),
           (case when t.icdText like '%y%' then 'Y' else 'NO-Y' end)

否则，替换为COUNT（*）：

count(distinct patientid)

这应该给你你需要的应急表中的信息。

Answer 2:

这里有一个替代方法：

select      RRTGotAlert         = case RRTGotAlert when 0 then 'N' when 1 then 'Y' end
            ,TZ_OnPilotUnit_N   = sum(TZ_OnPilotUnit_N)
            ,TZ_OnPilotUnit_Y   = sum(TZ_OnPilotUnit_Y)
from        (select     RRTGotAlert
                        ,TZ_OnPilotUnit_N = [0]
                        ,TZ_OnPilotUnit_Y = [1]
            from        #analysis
            pivot       (count(Encounter) for TZ_OnPilotUnit in ([0],[1])) pvt) got_alert
group by    case RRTGotAlert when 0 then 'N' when 1 then 'Y' end
order by    case RRTGotAlert when 0 then 'N' when 1 then 'Y' end