我正在创建一个SQL查询,它将根据两个聚合函数的值从表中提取记录.这些聚合函数是从同一个表中提取数据,但是具有不同的过滤条件.我遇到的问题是SUM的结果比仅包含一个SUM功能大得多.我知道我可以使用临时表创建这个查询,但我只是想知道是否有一个优雅的解决方案,只需要一个查询.
我创建了一个简化版本来演示这个问题.以下是表结构：
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
任何见解将不胜感激.
解决方法
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
编辑：
这不是什么大不了的,但我讨厌重复的条件,所以我们可以重构：
Select * From
(
SELECT
E.EMPID,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
这样,如果你改变你的情况,它只在一个地方.
