使用不同条件在同一张表中的一个SQL查询中有多个聚合函数 [英] Multiple aggregate functions in one SQL query from the same table using different conditions

问题描述

我正在创建一个SQL查询，该查询将基于两个聚合函数的值从表中提取记录。这些聚合函数从同一张表中提取数据，但过滤条件不同。我遇到的问题是，与仅包含一个SUM函数的情况相比，SUM的结果要大得多。我知道我可以使用临时表创建此查询，但是我只是想知道是否有一种优雅的解决方案，只需要单个查询即可。

I'm working on creating a SQL query that will pull records from a table based on the value of two aggregate functions. These aggregate functions are pulling data from the same table, but with different filter conditions. The problem that I run into is that the results of the SUMs are much larger than if I only include one SUM function. I know that I can create this query using temp tables, but I'm just wondering if there is an elegant solution that requires only a single query.

我已经创建了简化版本以演示该问题。表结构如下：

I've created a simplified version to demonstrate the issue. Here are the table structures:

EMPLOYEE TABLE

EMPID
1
2
3

ABSENCE TABLE

EMPID   DATE       HOURS_ABSENT
1       6/1/2009   3
1       9/1/2009   1
2       3/1/2010   2

这是查询：

SELECT
    E.EMPID
    ,SUM(ATOTAL.HOURS_ABSENT) AS ABSENT_TOTAL
    ,SUM(AYEAR.HOURS_ABSENT) AS ABSENT_YEAR

FROM
    EMPLOYEE E

    INNER JOIN ABSENCE ATOTAL ON
        ATOTAL.EMPID = E.EMPID

    INNER JOIN ABSENCE AYEAR ON
        AYEAR.EMPID = E.EMPID

WHERE
    AYEAR.DATE > '1/1/2010'

GROUP BY
    E.EMPID

HAVING
    SUM(ATOTAL.HOURS_ABSENT) > 10
    OR SUM(AYEAR.HOURS_ABSENT) > 3

任何见识都会受到赞赏。

Any insight would be greatly appreciated.

推荐答案

SELECT
    E.EMPID
    ,SUM(ABSENCE.HOURS_ABSENT) AS ABSENT_TOTAL
    ,SUM(case when year(Date) = 2010 then ABSENCE.HOURS_ABSENT else 0 end) AS ABSENT_YEAR

FROM
    EMPLOYEE E

    INNER JOIN ABSENCE ON
        ABSENCE.EMPID = E.EMPID

GROUP BY
    E.EMPID

HAVING
    SUM(ATOTAL.HOURS_ABSENT) > 10
    OR SUM(case when year(Date) = 2010 then ABSENCE.HOURS_ABSENT else 0 end) > 3

编辑：

不是没什么大不了的，但是我讨厌重复条件，所以我们可以像这样重构：

It's not a big deal, but I hate repeating conditions so we could refactor like:

Select * From
(
    SELECT
        E.EMPID
        ,SUM(ABSENCE.HOURS_ABSENT) AS ABSENT_TOTAL
        ,SUM(case when year(Date) = 2010 then ABSENCE.HOURS_ABSENT else 0 end) AS ABSENT_YEAR

    FROM
        EMPLOYEE E

        INNER JOIN ABSENCE ON
            ABSENCE.EMPID = E.EMPID

    GROUP BY
        E.EMPID
    ) EmployeeAbsences
    Where ABSENT_TOTAL > 10 or ABSENT_YEAR > 3

这样，如果您更改案情，就只能集中在一个地方。

This way, if you change your case condition, it's in one spot only.

这篇关于使用不同条件在同一张表中的一个SQL查询中有多个聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！