SQL 如何按两个相同的列显示和分组，其中哪一个过滤得更多？

Question

需要显示一些列，例如年、周、严重性、警报和与以前的警报相同的警报，只是过滤更多。如何添加最后一列，与上一列相同，只是过滤得更多？

例：

SELECT 
CAST(DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ) AS VARCHAR) AS YEAR,
CAST(DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ) AS VARCHAR) AS MONTH,
rSEV.NAME AS SEVERITY,
COUNT(rSTA.ALARMIDKEY) AS ALARMS
FROM 
    REPORTER.reporter.REPORTER_STATUS rSTA
INNER JOIN
    REPORTER.reporter.REP_SEVERITY_TYPES rSEV
    ON
    rSTA.ORIGINALSEVERITY = rSEV.SEVERITY
WHERE
    rSTA.FIRSTOCCURRENCEDAY > DATEADD(week, -10, GETDATE())
    AND
    rSEV.NAME != 'Clear'
GROUP BY
    DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ),
    DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ),
    rSEV.NAME
ORDER BY
    DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ) DESC,
    DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ) DESC

此查询正确生成前 4 列，附上图片：警报

第五列应与警报相同，但有条件：

SELECT ALARMIDKEY AS ALARMS_CC FROM REPORTER.reporter.REPORTER_STATUS WHERE
                    (MAINTMODECRONTAB != 'Y'
                     AND SUPPRESSESCL < 4
                     AND SPMAUTO != 1
                     AND ORIGINALSEVERITY > 0)
                     AND ((AIWAVER = 3 AND EVENTACTOR = 1)
                          OR
                          ((AIWAVER < 3 OR AIWAVER IS NULL)
                           AND ((CONTROLCENTREVIEW = 1
                                 AND ORIGINALSEVERITY = 5)
                                OR (CONTROLCENTREVIEW = 2)
                                OR (ALERTGROUP = 'CHECKLIST')
                               )
                          )
                         )

现在我想知道如何将此条件添加到主（第一个）查询中并按其分组，以便生成正确的数据。对于每年，每周和严重性显示警报和警报CC（同一列，只是不同的条件）。我希望它看起来像这样：ALARMS_CC

希望它能清楚地表明我想要实现的目标。如果实现这一目标的方法更加优化，那就太好了。也许加入是可能的？

Answer 1

您可以尝试使用本文中的案例语句 - 如何在SQL查询中按过滤器计数？。

所以查询将看起来像

    SELECT 
        CAST(DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ) AS VARCHAR) AS YEAR,
        CAST(DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ) AS VARCHAR) AS MONTH,
        rSEV.NAME AS SEVERITY,
        COUNT(rSTA.ALARMIDKEY) AS ALARMS,
        SUM(CASE WHEN rSTA.MAINTMODECRONTAB != 'Y'
                             AND rSTA.SUPPRESSESCL < 4
                             AND rSTA.SPMAUTO != 1
                             AND rSTA.ORIGINALSEVERITY > 0)
                             AND ((rSTA.AIWAVER = 3 AND rSTA.EVENTACTOR = 1)
                                  OR
                                  ((rSTA.AIWAVER < 3 OR rSTA.AIWAVER IS NULL)
                                   AND ((rSTA.CONTROLCENTREVIEW = 1
                                         AND rSTA.ORIGINALSEVERITY = 5)
                                        OR (rSTA.CONTROLCENTREVIEW = 2)
                                        OR (rSTA.ALERTGROUP = 'CHECKLIST')
                                       )
                                  ))
                    THEN 1
                    ELSE 0 END) AS ALARMS_CC
    FROM 
        REPORTER.reporter.REPORTER_STATUS rSTA
    INNER JOIN
        REPORTER.reporter.REP_SEVERITY_TYPES rSEV
        ON
        rSTA.ORIGINALSEVERITY = rSEV.SEVERITY
    WHERE
        rSTA.FIRSTOCCURRENCEDAY > DATEADD(week, -10, GETDATE())
        AND
        rSEV.NAME != 'Clear'
    GROUP BY
        DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ),
        DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ),
        rSEV.NAME
    ORDER BY
        DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ) DESC,
        DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ) DESC

更新

我在下面添加一个更优化的查询。

    WITH REPORTER_STATUS_FILTERED AS
    (
        SELECT ALARMIDKEY
        FROM REPORTER.reporter.REPORTER_STATUS
        WHERE MAINTMODECRONTAB != 'Y'
            AND SUPPRESSESCL < 4
            AND SPMAUTO != 1
            AND ORIGINALSEVERITY > 0)
            AND ((AIWAVER = 3 AND EVENTACTOR = 1)
                OR
                ((AIWAVER < 3 OR AIWAVER IS NULL)
                AND ((CONTROLCENTREVIEW = 1
                        AND ORIGINALSEVERITY = 5)
                    OR (CONTROLCENTREVIEW = 2)
                    OR (ALERTGROUP = 'CHECKLIST')
                    )
                ))
    )
    SELECT 
        CAST(DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ) AS VARCHAR) AS YEAR,
        CAST(DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ) AS VARCHAR) AS MONTH,
        rSEV.NAME AS SEVERITY,
        COUNT(rSTA.ALARMIDKEY) AS ALARMS,
        COUNT(rSTA1.ALARMIDKEY) AS ALARMS_CC
    FROM 
        REPORTER.reporter.REPORTER_STATUS rSTA
    INNER JOIN
        REPORTER.reporter.REP_SEVERITY_TYPES rSEV
        ON
        rSTA.ORIGINALSEVERITY = rSEV.SEVERITY
    LEFT JOIN
        REPORTER_STATUS_FILTERED rSTA1
        ON
        rSTA.ALARMIDKEY = rSTA1.ALARMIDKEY
    WHERE
        rSTA.FIRSTOCCURRENCEDAY > DATEADD(week, -10, GETDATE())
        AND
        rSEV.NAME != 'Clear'
    GROUP BY
        DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ),
        DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ),
        rSEV.NAME
    ORDER BY
        DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ) DESC,
        DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ) DESC

Answer 2

您可以通过子查询选择附加筛选警报来获取它们，如下所示：

SELECT 
    CAST(DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ) AS VARCHAR) AS YEAR,
    CAST(DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ) AS VARCHAR) AS MONTH,
    rSEV.NAME AS SEVERITY,
    COUNT(rSTA.ALARMIDKEY) AS ALARMS,
    filtered.ALARMS_CC
FROM REPORTER.reporter.REPORTER_STATUS rSTA
INNER JOIN REPORTER.reporter.REP_SEVERITY_TYPES rSEV
           ON rSTA.ORIGINALSEVERITY = rSEV.SEVERITY
INNER JOIN (SELECT COUNT(ALARMIDKEY) AS ALARMS_CC, 
                   DATEPART( yy, FIRSTOCCURRENCEDAY ) AS year, 
                   DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ) AS month,
                   ORIGINALSEVERITY AS os
            FROM REPORTER.reporter.REPORTER_STATUS 
            WHERE
                (MAINTMODECRONTAB != 'Y'
                 AND SUPPRESSESCL < 4
                 AND SPMAUTO != 1
                 AND ORIGINALSEVERITY > 0)
                 AND ((AIWAVER = 3 AND EVENTACTOR = 1)
                      OR
                      ((AIWAVER < 3 OR AIWAVER IS NULL)
                       AND ((CONTROLCENTREVIEW = 1
                             AND ORIGINALSEVERITY = 5)
                            OR (CONTROLCENTREVIEW = 2)
                            OR (ALERTGROUP = 'CHECKLIST')
                           )
                      )
                     )
            GROUP BY DATEPART( yy, FIRSTOCCURRENCEDAY ), 
                     DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ),
                     ORIGINALSEVERITY 
          ) AS filtered ON DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ) = filtered.year AND
                           DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ) = filtered.month AND
                           rSTA.ORIGINALSEVERITY = filtered.os
WHERE
    rSTA.FIRSTOCCURRENCEDAY > DATEADD(week, -10, GETDATE())
    AND
    rSEV.NAME != 'Clear'
GROUP BY
    DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ),
    DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ),
    rSEV.NAME
ORDER BY
    DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ) DESC,
    DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ) DESC

Answer 3

select YEAR, MONTH, SEVERITY, ALARMS, ALARMS_CC 
from (Q1) as T1 left join (Q2) as T2 on
T1.YEAR = T2.YEAR 
and T1.MONTH = T2.MONTH
and T1.SEVERITY = T1.SEVERITY
ORDER BY YEAR DESC, MON DESC

其中 Q1 是没有按部分排序的查询Q2 是具有附加条件的第一个查询，最后一个column_name更改。将 Q1、Q2 放入括号 （） 中很重要。