# 使用不同條件從同一個表中的一個SQL查詢中的多個聚合函數

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(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