统计每天不同类型数据的数量

原表结构

CREATE TABLE `car` (
  `id` varchar(50) NOT NULL,
  `car_name` varchar(22) DEFAULT '' COMMENT '车名称',
  `car_type` int(2) DEFAULT '0' COMMENT '车类型 0=摩托车，1= 小车，3=货车',
  `remark` varchar(128) DEFAULT '' COMMENT '备注',
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='车信息表';

统计每天不同车类型的总数列表

SELECT
	cast( create_date AS date ) date,
	SUM( CASE WHEN type = 0 THEN 1 ELSE 0 END ) 摩托车总数,
	SUM( CASE WHEN type = 1 THEN 1 ELSE 0 END ) 小车总数,
	SUM( CASE WHEN type = 2 THEN 1 ELSE 0 END ) 货车总数 
FROM
	car 
WHERE
	create_date BETWEEN '2021-12-01' 
	AND '2021-12-15' 
GROUP BY
	cast( create_date  AS date ) 
ORDER BY
	date DESC

结果