mysql - 如何写sql从三个表中选择总和并按日期分组

Question

我有 3 个表。 作品,软件,代理

它是这样显示的:

---

代理:

id  name
1   agent1
2   agent2
3   agent3
4   agent4

有效

id  name    date(TIMESTAMP type)    agent_id
1   w1  2014/1/1    1
2   w2  2014/2/2    1
3   w3  2014/1/3    2
4   w4  2014/1/4    2
5   w5  2014/1/5    3
6   w6  2014/1/6    4
7   w7  2014/1/7    4
8   w8  2014/1/8    4
9   w9  2014/3/9    4

软件

id  name    date(TIMESTAMP type)  agent_id
1   s1  2014/1/1    1
2   s2  2014/2/2    1
3   s3  2014/1/3    2
4   s4  2014/1/4    2
5   s5  2014/1/5    3
6   s6  2014/1/6    4
7   s7  2014/1/7    4
8   s8  2014/1/8    4
9   s9  2014/3/9    4

---

我想得到这样的结果:通过agent_id获取当年每个月的作品总和+软件连接

Jan-14  Feb-14  Mar-14  Apr-14  May-14  Jun-14  Jul-14  Aug-14  Sep-14  Oct-14  Nov-14  Dec-14
agent1  2   2   0   0   0   0   0   0   0   0   0   0
agent2  4   0   0   0   0   0   0   0   0   0   0   0
agent3  2   0   0   0   0   0   0   0   0   0   0   0
agent4  6   0   2   0   0   0   0   0   0   0   0   0

如何在MySql中写sql？

Answer 1

试试这个:

SELECT a.id, a.name, 
       SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201401 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201401 THEN 1 ELSE 0 END) AS Jan14, 
       SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201402 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201402 THEN 1 ELSE 0 END) AS Feb14, 
       SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201403 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201403 THEN 1 ELSE 0 END) AS Mar14, 
       SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201404 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201404 THEN 1 ELSE 0 END) AS Apr14, 
       SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201405 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201405 THEN 1 ELSE 0 END) AS May14, 
       SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201406 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201406 THEN 1 ELSE 0 END) AS Jun14, 
       SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201407 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201407 THEN 1 ELSE 0 END) AS Jul14, 
       SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201408 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201408 THEN 1 ELSE 0 END) AS Aug14, 
       SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201409 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201409 THEN 1 ELSE 0 END) AS Sep14, 
       SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201410 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201410 THEN 1 ELSE 0 END) AS Oct14, 
       SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201411 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201411 THEN 1 ELSE 0 END) AS Nov14, 
       SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201412 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201412 THEN 1 ELSE 0 END) AS Dec14
FROM agent a 
LEFT OUTER JOIN works w ON a.id = w.agent_id 
LEFT OUTER JOIN software s ON a.id = s.agent_id 
GROUP BY a.id;