使用不同条件在同一张表中的一个SQL查询中有多个聚合函数
我正在创建一个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.