mysql - 分组查询(透视式？)

Question

我以为这会很简单，但我很挣扎(可能是星期五的大脑)

我有一张 table :

Staff      department     job_date       job             Hours---------------------------------------------------------------Chris      Software       2011-03-02     Writing Code    3Chris      Software       2011-03-04     Holiday         7Bob        QA             2011-03-02     Testing         4Frank      Design         2011-03-11     Meeting         1

现在，我需要一个查询，该查询将根据工作字段中的内容按天“分组”结果，即..所以我得到每位员工在工作和假期上花费的时间总和:

结果集:

Staff          Worked Hours       Holiday Hours  Department---------------------------------------------------------------Chris           3                  7              SoftwareBob             4                  0              QAFrank           1                  0              Design---------------------------------------------------------------Total           8                  7

感谢您提供的任何帮助!

克里斯

Answer 1

SELECT  department, staff,
        SUM(IF(job = 'Holiday', 0, hours)) AS work_hours,
        SUM(IF(job = 'Holiday', hours, 0)) AS holiday_hours
FROM    mytable
GROUP BY
        department, staff WITH ROLLUP