使用左外连接的每个ID的SQL组行数

Question

我有一张表格，包含糖尿病，高血压，心力衰竭等6种不同条件下的近百万份索赔记录。每个成员都有一些索赔。他可能会声称患有糖尿病或高血压或其他疾病。我的目标是将每个成员行的条件（索赔数量）分组。

现有的表

+--------------+---------------+------+------------+ 
| Conditions | ConditionCode | ID | Member_Key | 
+--------------+---------------+------+------------+ 
| DM   |   3001 | 1212 | A1528  | 
| HTN   |   5001 | 1213 | A1528  | 
| COPD   |   6001 | 1214 | A1528  | 
| DM   |   3001 | 1215 | A1528  | 
| CAD   |   8001 | 1823 | B4354  | 
| HTN   |   5001 | 3458 | B4354  | 
+--------------+---------------+------+------------+ 

所需的结果

+------------+------+-----+----+----+-----+-----+ 
| Member_Key | COPD | CAD | DM | HF | CHF | HTN | 
+------------+------+-----+----+----+-----+-----+ 
| A1528  | 1 |  | 2 | |  | 1 | 
| B4354  |  | 1 | | |  | 1 | 
+------------+------+-----+----+----+-----+-----+ 

查询

select distinct tr.Member_Key,C.COPD,D.CAD,DM.DM,HF.HF,CHF.CHF,HTN.HTN 
    FROM myTable tr 
    --COPD 
    left outer join (select Member_Key,'X' as COPD 
    FROM myTable 
    where Condition=6001) C 
    on C.Member_Key=tr.Member_Key 
    --CAD 
    left outer join (.... 

现在我只是使用'X'。但我试图根据条件获得索赔数量来代替X.我不认为使用左外连接在搜索100万行并执行截然不同时效率更高。你有什么其他的办法解决这个

Answer 1

你不想这么多的子查询，这很容易与GROUP BY和case语句：

SELECT Member_Key 
     SUM(CASE WHEN Condition=6001 THEN 1 ELSE 0 END) AS COPD,    
     SUM(CASE WHEN Condition=3001 THEN 1 ELSE 0 END) AS DM, 
     SUM(CASE WHEN Condition=5001 THEN 1 ELSE 0 END) AS HTN, 
     SUM(CASE WHEN Condition=8001 THEN 1 ELSE 0 END) AS CAD 
FROM myTable 
GROUP BY Member_Key 

Answer 2

这是CASE statments理想的情况下：

SELECT tr.Member_Key, 
    SUM(CASE WHEN Condition=6001 THEN 1 ELSE 0 END) as COPD, 
    SUM(CASE WHEN Condition=6002 THEN 1 ELSE 0 END) as OtherIssue, 
    SUM(CASE etc.) 
FROM myTable tr 
GROUP BY tr.Member_Key 

Answer 3

这应该与PIVOT来完成，如：

SELECT * 
FROM 
(SELECT conditions, member_key 
FROM t) src 
PIVOT 
(COUNT (conditions) 
for conditions in ([COPD], [CAD], [DM], [HF], [CHF], [HTN])) pvt