我专门针对SQL Server,但是如果MySQL也有此信息,它将对您有所帮助。我已经找到了如何创建一列来计算两个连续行的列之间的差异,但是我感兴趣的是如何计算两个可明确识别的行中的一列之间的差异?

我知道这样做的一种方法,但是它需要深度查询嵌套和重复相同的基本查询。假设我有一个连接多个表的大型查询,最终将其归结为:

SELECT
    projectStatus AS [Project Status],
    COUNT(projectStatus) AS [# of Projects]
FROM
    projectList
GROUP BY projectStatus


假设它给出以下结果:

Project Status | # of Projects
------------------------------
Delayed        |           167
Delayed Known  |            83
On Time        |            92
Ahead          |            86


我想做的是附加一行,计算“延迟”和“延迟已知”行的“项目数”值之间的差,然后省略“延迟”行,如下所示:

Project Status | # of Projects
------------------------------
Delayed        |           167
Delayed Known  |            83
On Time        |            92
Ahead          |            86
Delayed Unknown|            84


根据第一个查询,我弄清楚如何执行此操作的方式如下:

SELECT
    projectStatus AS [Project Status],
    COUNT(projectStatus) AS [# of Projects]
FROM
    projectList
GROUP BY projectStatus
UNION
SELECT
    'Delayed Unknown' AS [Project Status],
    SUM([Sum Val]) AS [# of Projects]
FROM (
    SELECT
        [# of Projects] *
        CASE
            WHEN [Project Status] = 'Delayed' THEN 1
            WHEN [Project Status] = 'Delayed Known'] THEN -1
            ELSE 0
        END AS [Sum Val]
    FROM (
        SELECT
            projectStatus AS [Project Status],
            COUNT(projectStatus) AS [# of Projects]
        FROM
            projectList
        WHERE
            projectStatus IN ('Delayed', 'Delayed Known')
        GROUP BY projectStatus
    ) AS queryC
) AS queryB


请记住,本文简化了基于该查询的内部查询,但实际上它是一个更大的查询,由它自己的UNION组成。因此,这变得非常丑陋并接近难以维持的状态。

该查询的目标是充当SQL Server Reporting Services的数据集,因此我的约束是在一个查询中完成所有这些操作(除非可以在SSRS数据集中使用临时表)。因此,在一个查询中,是否存在一种侵入性较小的方法来进行此计算?

最佳答案

请尝试此操作,可能是您正在寻找以下类似内容。

;with cte as(
    select Projectstatus, count(*) as [# of Projects]
    from @Table
    where projectStatus IN ('Delayed', 'Delayed Known')
    group by ProjectStatus
), cte2 as(
    select
        (Select [# of Projects] From cte Where Projectstatus = 'Delayed')
        -(Select [# of Projects] From cte Where Projectstatus = 'Delayed Known')  as DelayedUnKownProjects
)
    select Projectstatus, [# of Projects]
    From cte
    UNION ALL
    SELECT 'Delayed UnKnown' as Projectstatus, DelayedUnKownProjects as [# of Projects]
    From cte2

10-05 21:15
查看更多