mysql查询没有数据为0_mysql查询 根据月份查询每天的数据 没有数据显示0-Go语言中文社区...

mysql 查询 年月 每天的数据 没有数据的为0

表名 ceshi

查询2017年11月份每天的shuliang的和

SELECT

DATE_FORMAT(lefttable.date, '%Y-%m') AS yearMonth,

lefttable.date,

IFNULL(righttable.m, '0') sumpdc

FROM

(

SELECT

ADDDATE(y. FIRST, x.d - 1) AS date

FROM

(

SELECT

01 AS d

UNION ALL

SELECT

02

UNION ALL

SELECT

03

UNION ALL

SELECT

04

UNION ALL

SELECT

05

UNION ALL

SELECT

06

UNION ALL

SELECT

07

UNION ALL

SELECT

08

UNION ALL

SELECT

09

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

CONCAT('2017-11', '-01') AS FIRST,

DAY (

LAST_DAY(

str_to_date('2017-11', '%Y-%m')

)

) AS last

) y

WHERE

x.d <= y.last

AND ADDDATE(y. FIRST, x.d - 1) <= CURDATE()

) AS lefttable

LEFT JOIN (

SELECT

sum(af2.shuliang) AS m,

DATE_FORMAT(af2.time, '%Y-%m-%d') AS gptime

FROM

ceshi af2

WHERE

DATE_FORMAT(af2.time, '%Y-%m') = '2017-11'

GROUP BY

DATE_FORMAT(af2.time, '%Y-%m-%d')

) AS righttable ON DATE_FORMAT(lefttable.date, '%Y-%m-%d') = righttable.gptime