mysql查询时间区间的数据统计
1、按天统计，统计区间的所有天数的数据，没有的赋值为0
SELECT
DATE_FORMAT( a.date, '%Y-%m-%d' ) AS abscissa_name,
IFNULL( b.count, 0 ) AS total
FROM
(
SELECT
@num := @num + 1 AS num,
date_format( adddate( date_sub( '2021-11-03', INTERVAL 1 DAY ), INTERVAL @num DAY ), '%Y-%m-%d' ) AS date FROM
rd_resource_application_detail,
( SELECT @num := 0 ) t
WHERE
adddate( date_sub( '2021-11-03', INTERVAL 1 DAY ), INTERVAL @num DAY ) < date_format( '2021-11-09', '%Y-%m-%d' ) ORDER BY
date
) a
2019高考文综LEFT JOIN (
SELECT
black mondaycount( * ) AS count,
DATE_FORMAT( apply_time, '%Y-%m-%d' ) AS date
FROM
`rd_resource_application_detail`
WHERE
DATE_FORMAT( apply_time, '%Y-%m-%d' ) BETWEEN '2021-11-03' AND '2021-11-09'
GROUP BY
date
) b ON a.date = b.date
plea wait是什么意思ORDER BY
a.date ASC
2、统计时间区间的数据，按⽉统计每⽉的数据，没有的赋值为0
SELECT
a.abscissa_name,
sum( count ) AS total
FROM
(
SELECT
*
FROM
(
SELECT
count( t.id ) AS count,
date_format( t.apply_time, '%Y-%m' ) AS abscissa_name
FROM
rd_resource_application_detail t
WHERE
t.state = 6
AND DATE_FORMAT( t.apply_time, '%Y-%m-%d' ) BETWEEN '2020-01-01' AND '2021-12-31' GROUP BY
DATE_FORMAT( t.apply_time, '%Y-%m' ) UNION ALL
SELECT
@uu := 0 AS count,
abscissa_name
FROM
(
SELECT
@num := @num + 1 AS number,
date_format( adddate( '2020-01-01', INTERVAL @num MONTH ), '%Y-%m' ) AS abscissa_name FROM
rd_resource_application_detail a,
( SELECT @num := - 1 ) t
WHERE
adddate( '2020-01-01', INTERVAL @num MONTH ) < adddate( '2021-12-31', INTERVAL - 1 MONTH ) ORDER BY
abscissa_name
) rr
) tt
ORDER BY
tt.abscissa_name
) a
GROUP BY
a.abscissa_name;
3、统计某天的24⼩时的数据，没有的赋值为0
SELECT
a.HOUR,
concat( IF ( a.HOUR < 10, concat( '0', a.HOUR ), a.HOUR ), ':00' ) AS abscissa_name, ifnull( b.count, 0 ) AS total
FROM
(
SELECT
0 AS HOUR UNION ALL
SELECT
1 AS HOUR UNION ALL
SELECT
2 AS HOUR UNION ALL
SELECT
3 AS HOUR UNION ALL
SELECT
4 AS HOUR UNION ALL
SELECT
5 AS HOUR UNION ALL
SELECT
6 AS HOUR UNION ALL
SELECT
7 AS HOUR UNION ALL
SELECT
8 AS HOUR UNION ALL
SELECT
9 AS HOUR UNION ALL
SELECT
10 AS HOUR UNION ALL
SELECT
11 AS HOUR UNION ALL
SELECT
SELECT
12 AS HOUR UNION ALL
SELECT
13 AS HOUR UNION ALL
SELECT
14 AS HOUR UNION ALL
SELECT
15 AS HOUR UNION ALL
SELECT
16 AS HOUR UNION ALL
SELECT
17 AS HOUR UNION ALL
SELECT
18 AS HOUR UNION ALL
SELECT
19 AS HOUR UNION ALL
SELECT
20 AS HOUR UNION ALL
SELECT
21 AS HOUR UNION ALL
SELECT
22 AS HOUR UNION ALL
SELECT
23 AS HOUR
) a
新年快乐的英文怎么写LEFT JOIN (
SELECT HOUR
( apply_time ) AS HOUR,
count( apply_time ) AS count
FROM
rd_resource_application_detail
WHERE
脱缰之马state = 6
AND DATE_FORMAT( apply_time, '%Y-%m-%d' ) = '2021-01-01' GROUP BY
date_format( apply_time, '%Y-%m-%d %h' ),
HOUR
)
b ON a.HOUR = b.HOUR
ORDER BY
HOUR ASC;
4、统计近多少天的数据，通过limt获取多少天的统计数据 LIMIT 15 ，15就是近多少天的数据
SELECT
t1.`day`,
t1.`day` as abscissa_name,
DATE_FORMAT(t1.`day`,"%Y-%m") as dayes,
COUNT( t2.id ) total
FROM
(
SELECT
@cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ) DAY
FROM
英语李老师
( SELECT @cdate := DATE_ADD( '2021-11-05', INTERVAL + 1 DAY ) FROM rd_resource_application_detail ) t0
LIMIT 15
) t1the interview 字幕
国旗下讲话 学雷锋LEFT JOIN (
SELECT
国际贸易专业研究生DATE( a.apply_time ) DAY,
a.id
FROM
rd_resource_application_detail a
WHERE a.state = 6 and
in the spotlight
DATE(a.apply_time) <= '2021-11-05' AND DATE(a.apply_time) > DATE_SUB( '2021-11-04', INTERVAL 1 DAY )
) t2 ON t2.DAY = t1.DAY
GROUP BY t1.`day`;
