代码之家 › 专栏 › 技术社区 › shockwave

SQL在不同条件下联接同一个表并获取计数

inner-join join sql-server sql

shockwave · 技术社区 · 7 年前

我有一张保险索赔表。它具有ID、患者的状况、ClaimID、EmergencyDepartmentFlag(1-ED;0-NonED)。我需要获得结果,其中给出了每个状况的索赔次数和每个状况的急诊就诊次数

+----------+-----------+----------+--------+
| MemberID | Condition | ClaimID  | EDFlag |
+----------+-----------+----------+--------+
| A123     | COPD      | 34124434 |      1 |
| A526     | COPD      | 34580304 |      0 |
| A693     | COPD      | 23723642 |      1 |
| A645     | DM        | 46534633 |      1 |
+----------+-----------+----------+--------+

预期产量

+-----------+-------------+----------+
| Condition | TotalClaims | EDClaims |
+-----------+-------------+----------+
| COPD      |           3 |        2 |
| DM        |           1 |        1 |
+-----------+-------------+----------+

查询

SELECT condition, 
       Count(a1.claimid) AS TotalClaims, 
       Count(a2.claimid) AS EDClaims 
FROM   (SELECT memberid, 
               condition, 
               claimid 
        FROM   mytable) a1 
       INNER JOIN (SELECT memberid, 
                          condition, 
                          claimid 
                   FROM   mytable 
                   WHERE  edflag = 1) a2 
               ON a1.memberid = A2.memberid 
GROUP  BY condition

此查询未返回正确的结果。

2 回复 | 直到 7 年前

Jayasurya Satheesh 7 年前

就用这个

SELECT
    Condition,
    TotalClaims = COUNT(1),
    EDClaims = SUM(CAST(EDFlag AS INT))
    FROM YourTable
    GROUP BY Condition

演示:

DECLARE @T TABLE
(
    Condition VARCHAR(50),
    ClaimID BIGINT,
    EDFlag BIT
)

INSERT INTO @T
VALUES('COPD',34124434,1),
('COPD',34580304,0),
('COPD',23723642,1),
('DM',46534633,1)

SELECT
    Condition,
    TotalClaims = COUNT(1),
    EDClaims = SUM(CAST(EDFlag AS INT))
    FROM @T
    GROUP BY Condition

后果

nickedeye 7 年前

SELECT 
Condition,
COUNT(*) AS TotalClaims,
SUM(CASE WHEN EDFlag = 1 THEN 1 ELSE 0 END) AS EDClaims
FROM mytable
GROUP BY Condition

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

3 月前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

3 月前

John D · 需要为NULL或NOT NULL的WHERE子句

4 月前

ojek · 如何对SQL结果进行分组和编号?

4 月前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

4 月前

Sax · 规范化Google表格(第一步)

4 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

4 月前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

4 月前

Andrus · 如何在sql中查找第二个匹配项

4 月前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

4 月前