mysqlql计算一段时间内 表中每天的数据量

发表于

原sql为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 SELECT
   CAST(tt.startTime AS char)	  x,count(tt.id) y
FROM
    (
        SELECT
            t.id id,
            date(t.start_time) startTime
        FROM
            sys_visit_log t
        WHERE
            t.prod_id IN (
                SELECT
                    xp.id
                FROM
                    xypt_production xp
                WHERE
                    xp.org_id = #{orgId,jdbcType=VARCHAR}
            )
    ) tt
where tt.startTime < date(NOW()) and tt.startTime >= date( DATE_SUB(NOW(), INTERVAL 7 DAY))
GROUP BY startTime
ORDER BY tt.startTime ASC
结果
1
2
3
4
2018-08-20	16
2018-08-22	21
2018-08-23	15
2018-08-24	6

修改后改为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SELECT
	CAST(a.x AS CHAR) x,
	count(tt.id) y
FROM
	(
		SELECT date( DATE_SUB(NOW(), INTERVAL 1 DAY) ) x
		UNION ALL
		SELECT date( DATE_SUB(NOW(), INTERVAL 2 DAY) ) x
		UNION ALL
		SELECT date( DATE_SUB(NOW(), INTERVAL 3 DAY) ) x
		UNION ALL
		SELECT date( DATE_SUB(NOW(), INTERVAL 4 DAY) ) x
		UNION ALL
		SELECT date( DATE_SUB(NOW(), INTERVAL 5 DAY) ) x
		UNION ALL
		SELECT date( DATE_SUB(NOW(), INTERVAL 6 DAY) ) x
		UNION ALL
		SELECT date( DATE_SUB(NOW(), INTERVAL 7 DAY) ) x
	) a
LEFT JOIN (
	SELECT
		t.id id,
		date(t.start_time) startTime
	FROM
		sys_visit_log t
	WHERE
		t.prod_id IN (
			SELECT 	xp.id
			FROM xypt_production xp
			WHERE xp.org_id = 'a4b1ccf70e4c47978e7931356ab43577'
		)
	AND date(t.start_time) < date(NOW())
	AND date(t.start_time) >= date(
		DATE_SUB(NOW(), INTERVAL 7 DAY)
	)
) tt ON a.x = tt.startTime
GROUP BY a.x
ORDER BY a.x ASC
结果
1
2
3
4
5
6
7
2018-08-20	16
2018-08-21	0
2018-08-22	21
2018-08-23	15
2018-08-24	6
2018-08-25	0
2018-08-26	0

参考地址

https://stackoverflow.com/questions/16636433/mysql-count-to-return-0-if-no-records-found