mysql根据时间统计数据，没有数据显示为0

本是古典何须时尚 2023-10-12 11:24 9阅读 0赞

*  ## **要求1** ##

> mysql根据时间统计数据，没有数据显示为0，时间格式为：年-月。可对此进行改造，使其时间格式为：年-月-日。

**mysql语句**

SELECT
    	count( a.id ) AS count,
    	s.date 
    FROM
    	(
    	SELECT
    		DATE_FORMAT( m1, '%Y-%m' ) AS date 
    	FROM
    		(
    		SELECT
    			( CONCAT( '2020-05', '-01' ) - INTERVAL DAYOFMONTH( CONCAT( '2020-05-01', '-01' ))- 1 DAY ) + INTERVAL m MONTH AS m1 
    		FROM
    			(
    			SELECT
    				@rownum := @rownum + 1 AS m 
    			FROM
    				( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t1,
    				( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t2,
    				( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t3,
    				( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t4,
    				( SELECT @rownum :=- 1 ) t0 
    			) d1 
    		) d2 
    	WHERE
    		m1 <= CONCAT( '2023-09', '-01' ) 
    	ORDER BY
    		m1 
    	) s
    	LEFT JOIN table a ON s.date = DATE_FORMAT( table.date, '%Y-%m' ) 
    GROUP BY
    	s.date 
    ORDER BY
    	s.date

SELECT
    	count( a.id ) AS num,
    	s.date 
    FROM
    	(
    	SELECT
    		DATE_FORMAT( m1, '%Y-%m-%d' ) AS date 
    	FROM
    		(
    		SELECT
    			( CONCAT( '2020-05-01', '-01' ) - INTERVAL DAYOFMONTH( CONCAT( '2000-05-01', '-01' ))- 1 DAY ) + INTERVAL m DAY AS m1 
    		FROM
    			(
    			SELECT
    				@rownum := @rownum + 1 AS m 
    			FROM
    				( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t1,
    				( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t2,
    				( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t3,
    				( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t4,
    				( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t5,
    				( SELECT @rownum :=- 1 ) t0 
    			) d1 
    		) d2 
    	WHERE
    		m1 <= CONCAT( '2022-09-01', '-01' ) 
    	ORDER BY
    		m1 
    	) s
    	LEFT JOIN sys_cert_log a ON s.date = DATE_FORMAT( a.start_time, '%Y-%m-%d' ) 
    	AND a.hospital_id = '59' 
    GROUP BY
    	s.date 
    ORDER BY
    	s.date

*  ## 要求2 ##

> 以小时为统计单位

**mysql语句**

SELECT
    	COUNT( 1 ) AS totalTimes,
    	DATE_FORMAT( a.INSERT_TIME, '%H:00' ) AS hourCount 
    FROM
    	COMPANY_LOG a
    	LEFT JOIN tenant_user_info b ON a.COMPANY_ID = b.TENANT_INFO_ID 
    WHERE
    	a.IS_CHARGE = '0' 
    GROUP BY
    	hourCount