SQL -计算所有可能的组合

Question

我有一张桌子，里面有诊断和病人。该表具有每个诊断的一行，其中包含患者id和诊断。

例如

Patient ID | Diagnosis
-----------|----------
0          | Asthma
1          | Hypertension
1          | Asthma
2          | Asthma
2          | Hypertension
2          | Cancer
3          | Asthma

我想输出一些类似如下的内容

Asthma                          | 4
Hypertension                    | 2
Asthma & Hypertension           | 2
Asthma, Hypertension and Cancer | 1

如何使用诊断名称计算诊断组合的数量？

例如，x个患者有哮喘，x个患者有哮喘和高血压，x个患者有糖尿病、心脏病、肺部疾病、癌症等。

一些患者有多达12种诊断。提前谢谢你！

Answer 1

这可能需要比我更精通使用FOR XML连接行的人进行编辑，但这是一种方法：

WITH CTE AS (
SELECT
PatientID,
STUFF(
    (
    SELECT ', ' + [Diagnosis] 
    FROM Table 
    WHERE (PatientID = B.PatientID) 
    ORDER BY Diagnosis
    FOR XML PATH('')
    )
    ,1,1,'') AS Diagnoses
FROM Table B
  )

SELECT Diagnoses,COUNT(DISTINCT PatientID) as Total
FROM CTE
GROUP BY Diagnoses

基本上，在按诊断排序之后，您将为每个患者的所有诊断创建一个串联的值(这样就不会以不同于'Condition2,Condition1'的方式读取'Condition1,Condition2' )，然后创建第二个查询来获取每个组合的计数

Answer 2

如果我理解正确的话，您可以使用递归查询来完成此操作：

with recursive all_diags as (
   select patient_id, diagnosis, diagnosis as diagnosis_list
   from diagnostics
   union all 
   select c.patient_id, c.diagnosis, p.diagnosis_list||','||c.diagnosis
   from diagnostics c
     join all_diags p on p.patient_id = c.patient_id and p.diagnosis < c.diagnosis
)
select diagnosis_list, count(*)
from all_diags
group by diagnosis_list
order by diagnosis_list;

注意，关键字recursive是SQL标准所必需的，但并不是所有的数据库管理系统都支持使用该关键字。

不过，您的预期输出有一个区别:我的解决方案还返回您没有包含在样例输出中的组合：Cancer、Cancer,Hypertension和Asthma,Cancer。

下面是一个SQLFiddle示例：http://sqlfiddle.com/#!15/21087/1