我想添加表示其他表中的月份基数的列。
我有2张桌子。
休假申请
leaveid Userid
1 3
2 4
3 5
4 1
休假日期
dateid leaveid leavedates
1 1 2015-10-06
2 1 2015-10-07
3 2 2015-11-01
4 2 2015-11-02
5 3 2015-01-01
6 4 2015-02-12
我想以基于月份的总休假数结束：
userid january fabruary march so on...
1 1 3 1
2 2 0 1
2 3 4 1
对条目进行条件求和，如下所示：
SELECT a.userid,
SUM(IF(MONTH(leavedates) = 1, 1, 0)) AS january,
SUM(IF(MONTH(leavedates) = 2, 1, 0)) AS february,
SUM(IF(MONTH(leavedates) = 3, 1, 0)) AS march,
SUM(IF(MONTH(leavedates) = 4, 1, 0)) AS april,
SUM(IF(MONTH(leavedates) = 5, 1, 0)) AS may,
SUM(IF(MONTH(leavedates) = 6, 1, 0)) AS june,
SUM(IF(MONTH(leavedates) = 7, 1, 0)) AS july,
SUM(IF(MONTH(leavedates) = 8, 1, 0)) AS august,
SUM(IF(MONTH(leavedates) = 9, 1, 0)) AS september,
SUM(IF(MONTH(leavedates) = 10, 1, 0)) AS october,
SUM(IF(MONTH(leavedates) = 11, 1, 0)) AS november,
SUM(IF(MONTH(leavedates) = 12, 1, 0)) AS december
FROM Leave_application a
LEFT OUTER JOIN Leave_dates b
ON a.leaveid = b.leaveid
GROUP BY a.userid
执行如下查询操作：-
SELECT la.`userid`,
SUM(IF(MONTH(ld.`leavedates`) = 1, 1, 0)) as january,
SUM(IF(MONTH(ld.`leavedates`) = 2, 1, 0)) as february,
SUM(IF(MONTH(ld.`leavedates`) = 3, 1, 0)) as march,
SUM(IF(MONTH(ld.`leavedates`) = 4, 1, 0)) as april,
SUM(IF(MONTH(ld.`leavedates`) = 5, 1, 0)) as may,
SUM(IF(MONTH(ld.`leavedates`) = 6, 1, 0)) as june,
SUM(IF(MONTH(ld.`leavedates`) = 7, 1, 0)) as july,
SUM(IF(MONTH(ld.`leavedates`) = 8, 1, 0)) as augest,
SUM(IF(MONTH(ld.`leavedates`) = 9, 1, 0)) as september,
SUM(IF(MONTH(ld.`leavedates`) = 10, 1, 0)) as october,
SUM(IF(MONTH(ld.`leavedates`) = 11, 1, 0)) as november,
SUM(IF(MONTH(ld.`leavedates`) = 12, 1, 0)) as december
FROM `leave_application` as la
LEFT JOIN `leave_dates` as ld
ON (ld.`leaveid` = la.`leaveid`)
GROUP BY ld.`leaveid`