MySQL -按星期比较月份的数据-例如每个月第一个星期六的会话

Question

我有一个表记录，当客户端来了一个会话。我想创建一个图表来比较去年完成了多少次会话。我弄清楚了如何使用以下sql获取每个月的结果:

SELECT
DAY(`SessionDate`) as month_day, 
SUM(if(MONTH(SessionDate) =  1, 1, 0))  AS Jan,
SUM(if(MONTH(SessionDate) =  2, 1, 0))  AS Feb,
SUM(if(MONTH(SessionDate) =  3, 1, 0))  AS Mar,
SUM(if(MONTH(SessionDate) =  4, 1, 0))  AS Apr,
SUM(if(MONTH(SessionDate) =  5, 1, 0))  AS May,
SUM(if(MONTH(SessionDate) =  6, 1, 0))  AS Jun,
SUM(if(MONTH(SessionDate) =  7, 1, 0))  AS Jul,
SUM(if(MONTH(SessionDate) =  8, 1, 0))  AS Aug,
SUM(if(MONTH(SessionDate) =  9, 1, 0))  AS Sep,
SUM(if(MONTH(SessionDate) = 10, 1, 0))  AS 'Oct',
SUM(if(MONTH(SessionDate) = 11, 1, 0))  AS 'Nov',
SUM(if(MONTH(SessionDate) = 12, 1, 0))  AS 'Dec'
FROM sessions
WHERE
`SessionDate` >= NOW()-Interval 12 MONTH
GROUP BY DAY(`SessionDate`)
ORDER BY DAY(SessionDate)

结果如下:

------------------------------...
|month_day | Jan | Feb | Mar |...
------------------------------...
|         1|  1  |  2  |  0  |...
|         2|  2  |  3  |  1  |...
|         3|  0  |  4  |  1  |...
|         4|  0  |  4  |  2  |...
|         5|  4  |  3  |  3  |...
|         6|  2  |  1  |  4  |...
|         7|  2  |  0  |  4  |...
|         8|  1  |  2  |  4  |...
...

然而，这比较了1月1日到2月1日到3月1日…我想看到和比较的是每个月的第一个星期一有多少次会议…

1。1月1日是星期五。二月是星期二，三月是星期三，那么我的结果应该是这样的:

-----------------------------...
|weekday  | Jan | Feb | Mar |...
-----------------------------...
| 1st Mon |     |     |     |...
| 1st Tue |     |  2  |     |...
| 1st Wed |     |  3  |  1  |...
| 1st Thu |     |  4  |  0  |...
| 1st Fri |  1  |  4  |  1  |...
| 1st Sat |  2  |  3  |  1  |...
| 1st Sun |  0  |  1  |  2  |...
| 2nd Mon |  0  |  0  |  3  |...
| 2nd Tue |  4  |  2  |  4  |...
| 2nd Wed |  1  |  2  |  4  |...
| 2nd Thu |  1  |  2  |  4  |...
| 2nd Fri |  3  |  3  |  2  |...
| 2nd Sat |  4  |  1  |  0  |...
| 2nd Sun |  2  |  1  |  0  |...
| 3rd Mon |  0  |  0  |  1  |...
| 3rd Tue |  1  |  0  |  4  |...
...      etc

换句话说，图表将显示哪些工作日是受欢迎的(例如每个月的第一个星期六比第三个星期六有更多的会话)我用php和谷歌静态图表绘制图表，所以这是没有问题的。让每月的数据偏移，使工作日在同一行是我不能弄清楚的。希望我的问题有意义。我是一个业余程序员和新的stackoverflow，所以任何帮助将不胜感激。

Answer 1

这是我想出的解决方案。它看起来有点笨重，但很好用。我仍然愿意考虑更好的解决办法。

SELECT  
a.m AS CalPos, /* Calendar Position (not date) 
e.g. if the month starts on a Tues, 
the 1st would display in CalPos 2 */
COALESCE(b.Jan,0) AS 'Jan',
COALESCE(b.Feb,0) AS 'Feb',
COALESCE(b.Mar,0) AS 'Mar',
COALESCE(b.Apr,0) AS 'Apr',
COALESCE(b.May,0) AS 'May',
COALESCE(b.Jun,0) AS 'Jun',
COALESCE(b.Jul,0) AS 'Jul',
COALESCE(b.Aug,0) AS 'Aug',
COALESCE(b.Sep,0) AS 'Sep',
COALESCE(b.Oct,0) AS 'Oct',
COALESCE(b.Nov,0) AS 'Nov',
COALESCE(b.Dec,0) AS 'Dec'
FROM (
SELECT 1 AS m 
UNION SELECT 2 AS m 
UNION SELECT 3 AS m 
UNION SELECT 4 AS m 
UNION SELECT 5 AS m 
UNION SELECT 6 AS m 
UNION SELECT 7 AS m 
UNION SELECT 8 AS m 
UNION SELECT 9 AS m 
UNION SELECT 10 AS m 
UNION SELECT 11 AS m 
UNION SELECT 12 AS m
UNION SELECT 13 AS m
UNION SELECT 14 AS m
UNION SELECT 15 AS m
UNION SELECT 16 AS m
UNION SELECT 17 AS m
UNION SELECT 18 AS m
UNION SELECT 19 AS m
UNION SELECT 20 AS m
UNION SELECT 21 AS m
UNION SELECT 22 AS m
UNION SELECT 23 AS m
UNION SELECT 24 AS m
UNION SELECT 25 AS m
UNION SELECT 26 AS m
UNION SELECT 27 AS m
UNION SELECT 28 AS m
UNION SELECT 29 AS m
UNION SELECT 30 AS m
UNION SELECT 31 AS m
UNION SELECT 32 AS m
UNION SELECT 33 AS m
UNION SELECT 34 AS m
UNION SELECT 35 AS m
UNION SELECT 36 AS m
UNION SELECT 37 AS m
UNION SELECT 38 AS m
) AS a
LEFT JOIN 
( SELECT 
CASE
WHEN
(7 - MOD((9-DAYOFWEEK(CAST(DATE_SUB(CONCAT(DATE_FORMAT(SessionDate,'%Y-%m-'),'01'), INTERVAL 7 DAY) AS DATE) )),7)) = 7
THEN
(DAY(`SessionDate`)+( MOD((9-DAYOFWEEK(CAST(DATE_SUB(CONCAT(DATE_FORMAT(SessionDate,'%Y-%m-'),'01'), INTERVAL 7 DAY) AS DATE) )),7)))
ELSE
(DAY(`SessionDate`)+(7 - MOD((9-DAYOFWEEK(CAST(DATE_SUB(CONCAT(DATE_FORMAT(SessionDate,'%Y-%m-'),'01'), INTERVAL 7 DAY) AS DATE) )),7)))
END AS CalPos,   
SUM(IF(MONTH(SessionDate) =  1, 1, 0))  AS 'Jan',
SUM(IF(MONTH(SessionDate) =  2, 1, 0))  AS 'Feb',
SUM(IF(MONTH(SessionDate) =  3, 1, 0))  AS 'Mar',
SUM(IF(MONTH(SessionDate) =  4, 1, 0))  AS 'Apr',
SUM(IF(MONTH(SessionDate) =  5, 1, 0))  AS 'May',
SUM(IF(MONTH(SessionDate) =  6, 1, 0))  AS 'Jun',
SUM(IF(MONTH(SessionDate) =  7, 1, 0))  AS 'Jul',
SUM(IF(MONTH(SessionDate) =  8, 1, 0))  AS 'Aug',
SUM(IF(MONTH(SessionDate) =  9, 1, 0))  AS 'Sep',
SUM(IF(MONTH(SessionDate) = 10, 1, 0))  AS 'Oct',
SUM(IF(MONTH(SessionDate) = 11, 1, 0))  AS 'Nov',
SUM(IF(MONTH(SessionDate) = 12, 1, 0))  AS 'Dec'
FROM sessions
WHERE
`SessionDate` >= NOW() - INTERVAL 12 MONTH
GROUP BY CalPos
ORDER BY CalPos

) AS b
ON a.m = b.CalPos