两个不同的where子句的SQL Server查询

问题描述：

我想合并两个非常相似的查询。唯一不同的就是WHERE子句中：两个不同的where子句的SQL Server查询

SELECT 
    CONVERT(VARCHAR(10), Date, 101) as Date, 
    COUNT(*) as WinnerCount 
FROM 
    table t1 
WHERE 
    t1.Result = 'Winner' 
GROUP BY 
    CONVERT(VARCHAR(10), Date, 101)

，第二个具有失败者条款：

SELECT 
    CONVERT(VARCHAR(10), Date, 101) as Date, 
    COUNT(*) as LoserCount 
FROM 
    table t1 
WHERE 
    t1.Result = 'Loser' 
GROUP BY 
    CONVERT(VARCHAR(10), Date, 101)

我试图做到的，是得到一个结果集是这样的：

Date  | WinnerCount | LoserCount 
01/01/2017  24     16 
17/02/2017  13     9

我试图合并这与加入两个结果集：

SELECT 
    FirstSet.Date 
FROM 
    (SELECT 
     CONVERT(VARCHAR(10), Date, 101) as Date, 
     COUNT(*) as Count 
    FROM 
     table t1 
    WHERE 
     t1.Result = 'Winner' 
    GROUP BY 
     CONVERT(VARCHAR(10), Date, 101)) AS FirstSet 
JOIN 
    (SELECT 
     CONVERT(VARCHAR(10), Date, 101) as Date, 
     COUNT(*) as Count 
    FROM 
     table t1 
    WHERE 
     t1.Result = 'Loser' 
    GROUP BY 
     CONVERT(VARCHAR(10), Date, 101)) AS SecondSet ON FirstSet.Date = SecondSet.Date 
ORDER BY 
    FirstSet.Date

但在结果中我只有日期：/

答

您可以使用此查询。计算所有行的insted的，你可以总计所需的文本的所有匹配（*赢家”或‘在这种情况下失败者’）

SELECT 
    CONVERT(VARCHAR(10), Date, 101) as Date, 
    SUM(CASE WHEN t1.Result = 'Winner' THEN 1 ELSE 0 END) as WinnerCount, 
    SUM(CASE WHEN t1.Result = 'Loser' THEN 1 ELSE 0 END) as LoserCount 
from table t1 
where t1.Result in ('Winner', 'Loser') 
group by 
    CONVERT(VARCHAR(10), Date, 101)

答

你可以尝试类似：

SELECT 
    CONVERT(VARCHAR(10), Date, 101) as Date, 
    SUM(CASE WHEN t1.Result = 'Winner' THEN 1 ELSE 0) AS WinnerCount, 
    SUM(CASE WHEN t1.Result = 'Loser' THEN 1 ELSE 0) AS LoserCount 
FROM 
    table t1 
group by 
    CONVERT(VARCHAR(10), Date, 101)

答

以上所接受的答案将为您提供正确的结果，但是如果您想保留计数并且不使用总和来汇总结果，则可以使用以下选项：

select 
    CONVERT(VARCHAR(10), Date, 101) as Date, 
    COUNT(CASE WHEN t1.Result = 'Winner' THEN 1 ELSE NULL END) as WinnerCount, 
    COUNT(CASE WHEN t1.Result = 'Loser' THEN 1 ELSE NULL END) as LoserCount 
from table as t1 
where t1.Result in ('Winner', 'Loser') 
group by 
    CONVERT(VARCHAR(10), Date, 101)

两个不同的where子句的SQL Server查询

相关推荐