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

debugcn 发表于 Dev

科里

我正在尝试合并两个非常相似的查询。唯一不同的是在WHERE子句中：

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)

第二个带有Loser条款：

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

我试图将其与 join 合并为两个结果集：

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 计算所有行，您可以求和所有出现的所需文本（在这种情况下，*Winner”或“Loser”）。

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)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。