mysql本周数据没有填充_MySql查询本周、本月、本年数据（没有数据则补全0）

最近写项目，发现有很多图表统计，需要查询本周、本月、本年数据。但是图表需要两个数组，一个日期数组，一个数据数组。然而数据库查询却只能查询出有数据的日期数据，所以找了很多资料终于能有补全日期和数据的方法。这样查询sql性能可能不太好，慎重使用~~。有更好的建议或者优化欢迎各位大神留言~不多说直接贴SQL语句

–查询本周数据(在国内是周一至周日，在国外是周日至周六。所以这里按国内来)

SELECT

CASE datas.weeks

WHEN '1' THEN

'星期一'

WHEN '2' THEN

'星期二'

WHEN '3' THEN

'星期三'

WHEN '4' THEN

'星期四'

WHEN '5' THEN

'星期五'

WHEN '6' THEN

'星期六'

WHEN '0' THEN

'星期日'

END AS 'week',

IFNULL(datas.totalUser, 0) totalUser

FROM

(

SELECT

a.d weeks,

b.totalUser

FROM

(

SELECT

0 AS d

UNION ALL

SELECT

1

UNION ALL

SELECT

2

UNION ALL

SELECT

3

UNION ALL

SELECT

4

UNION ALL

SELECT

5

UNION ALL

SELECT

6

) a

LEFT JOIN (

SELECT

date_format(us.ctime, '%w') AS datetime,

COUNT(us.usersId) AS totalUser

FROM

users us

WHERE

yearweek(now() - INTERVAL 1 DAY) = yearweek(ctime - INTERVAL 1 DAY)

GROUP BY

date_format(us.ctime, '%w')

) b ON a.d = b.datetime

) datas

ORDER BY

datas.weeks ASC

–查询本月数据(按天数查询)

SELECT a.d ctime,IFNULL(b.totalUser,0)totalUser FROM (SELECT ADDDATE(y.first, x.d - 1) as d

FROM

(

SELECT 1 AS d UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 9 UNION ALL

SELECT 10 UNION ALL

SELECT 11 UNION ALL

SELECT 12 UNION ALL

SELECT 13 UNION ALL

SELECT 14 UNION ALL

SELECT 15 UNION ALL

SELECT 16 UNION ALL

SELECT 17 UNION ALL

SELECT 18 UNION ALL

SELECT 19 UNION ALL

SELECT 20 UNION ALL

SELECT 21 UNION ALL

SELECT 22 UNION ALL

SELECT 23 UNION ALL

SELECT 24 UNION ALL

SELECT 25 UNION ALL

SELECT 26 UNION ALL

SELECT 27 UNION ALL

SELECT 28 UNION ALL

SELECT 29 UNION ALL

SELECT 30 UNION ALL

SELECT 31

) x,

(

SELECT date(now()) - INTERVAL DAY(date(now())) - 1 DAY AS first,

DAY(LAST_DAY(date(now()))) AS last) y

WHERE x.d <= y.last)a left join (

select date(us.ctime) as datetime, COUNT(us.usersId) as totalUser

from users us group by date(us.ctime)

) b on a.d = b.datetime ORDER BY date(a.d) asc

查询本年数据(按月份查询)

SELECT

CASE datas.weeks

WHEN '1' THEN

'一月'

WHEN '2' THEN

'二月'

WHEN '3' THEN

'三月'

WHEN '4' THEN

'四月'

WHEN '5' THEN

'五月'

WHEN '6' THEN

'六月'

WHEN '7' THEN

'七月'

WHEN '8' THEN

'八月'

WHEN '9' THEN

'九月'

WHEN '10' THEN

'十月'

WHEN '11' THEN

'十一月'

WHEN '12' THEN

'十二月'

END AS 'week',

IFNULL(datas.totalUser, 0) totalUser

FROM

(

SELECT

a.d weeks,

b.totalUser

FROM

(

SELECT

1 AS d

UNION ALL

SELECT

2

UNION ALL

SELECT

3

UNION ALL

SELECT

4

UNION ALL

SELECT

5

UNION ALL

SELECT

6 UNION ALL

SELECT

7 UNION ALL

SELECT

8 UNION ALL

SELECT

9 UNION ALL

SELECT

10 UNION ALL

SELECT

11 UNION ALL

SELECT

12

) a

LEFT JOIN (

SELECT

date_format(us.ctime, '%m') AS datetime,

COUNT(us.usersId) AS totalUser

FROM

users us

WHERE

YEAR(us.ctime)=YEAR(NOW())

GROUP BY

datetime

) b ON a.d = b.datetime

) datas

ORDER BY

datas.weeks ASC