尝试使用公用表表达式对列标题的别名求和

Question

>我有一个数据表，其中记录了几周内的通过和失败。

我想计算每周的尝试次数，以及通过和失败

的次数，然后将这些通过和失败的总和作为检查。

SELECT week, 
    COUNT (Week) as weekcount, 
    sum(case when result = 'pass' then 1 else 0 end) as 'passcount' ,
    sum(case when result = 'fail' then 1 else 0 end) as 'failcount',
        sum(case when result = 'pass' then 1 else 0 end) +
        sum(case when result = 'fail' then 1 else 0 end) as 'sum'
FROM table1
group by week
order by week

上面的代码片段做了我想要的，但我一直在阅读有关 CTE 的信息，作为在我的查询中重复的替代方案。

我希望能够在查询中使用别名"passcount"和"failcount"，而无需将它们作为列添加到表中。我试图在下面首先建立CTE。

with sums as
(
    select result,
    sum(case when result = 'pass' then 1 else 0 end) as 'passcount' ,
    sum(case when result = 'fail' then 1 else 0 end) as 'failcount',
    from table1 
)

上面报告了结尾右括号附近的语法错误，但我不明白为什么。

谁能对此有任何了解或更好的方法？

Answer 1

我不确定你为什么要考虑 CTE，你可以在一组中做到这一点：

SELECT 
        [Week], 
        COUNT([Week]) AS weekcount
    ,   SUM(CASE WHEN result = 'pass' THEN 1 ELSE 0 END) AS passcount   
    ,   SUM(CASE WHEN result = 'fail' THEN 1 ELSE 0 END) AS failcount
    ,   COUNT(*) AS TotalSum
FROM table1
GROUP BY [Week]

在 SQL Server 中，不能引用来自同一 SELECT 的别名，例如 passcount、weekcount。等。这是您需要一种解决方法的方式，例如使用子查询、CTE、联接、交叉应用或任何其他可用于此目的的方法。

以下是一些常用方法：

使用子查询：

SELECT 
    [Week]
,   SUM(weekcount) weekcount
,   SUM(passcount) passcount
,   SUM(failcount) failcount
,   SUM(passcount + failcount) AS TotalSum
FROM (
    SELECT 
        [Week], 
        COUNT([Week]) AS weekcount
    ,   SUM(CASE WHEN result = 'pass' THEN 1 ELSE 0 END) AS passcount   
    ,   SUM(CASE WHEN result = 'fail' THEN 1 ELSE 0 END) AS failcount
    FROM table1
    GROUP BY [Week]
) D 
GROUP BY 
    [Week]

使用 CTE 方法：

;WITH CTE AS (
    SELECT 
        [Week], 
        COUNT([Week]) AS weekcount
    ,   SUM(CASE WHEN result = 'pass' THEN 1 ELSE 0 END) AS passcount   
    ,   SUM(CASE WHEN result = 'fail' THEN 1 ELSE 0 END) AS failcount
    FROM table1
    GROUP BY [Week]
)
SELECT      
        [Week]
    ,   SUM(weekcount) weekcount
    ,   SUM(passcount) passcount
    ,   SUM(failcount) failcount
    ,   SUM(passcount + failcount) AS TotalSum
FROM CTE 
GROUP BY 
    [Week]

使用交叉应用方法：请参阅@Gordon Linoff答案（基本和直接）。

这些是您可以在您的情况下使用的最简单的方法，您也可以在您的情况下使用 PIVOT，但为了简单起见，我没有提到它。

Answer 2

在SQL Server中，我认为横向连接（即 cross apply ） 是一种比子查询和 CTE 更简洁的方法。 在您的情况下，您可以在聚合之前生成标志，如下所示：

select week, count(*) as weekcount, 
       sum(ispass) as passcount,
       sum(isfail) as failcount,
       (sum(ispass) + sum(isfail)) as passfailcount
from table1 t1 cross apply
     (values (case when result = 'pass' then 1 else 0 end,
              case when result = 'fail' then 1 else 0 end
             )
     ) v(ispass, isfail)
group by week
order by week

Answer 3

你只需要再选择一个

with sums as
(
    select result,
    sum(case when result = 'pass' then 1 else 0 end) as 'passcount' ,
    sum(case when result = 'fail' then 1 else 0 end) as 'failcount',
    from table1 
)
select result, passcount, failcount, passcount + failcount as sum
from sums;

Answer 4

你实际上不需要同时编写两个CASE表达式，你可以使用一个IN：

SELECT Week,
       COUNT(Week) AS weekcount,
       COUNT(CASE Result WHEN 'pass' THEN 1 END) AS PassCount,
       COUNT(CASE Result WHEN 'fail' THEN 1 END) AS FailCount,
       COUNT(CASE WHEN Result IN ('pass','fail') THEN 1 END) AS PassFailCount
FROM Table1
GROUP BY Week
ORDER BY Week;

是的，这并没有向您展示如何使用 CTE，但是，逻辑比重复两个CASE表达式"更简洁"（更简洁）。