mysql横竖表转换

订单根据部门和状态分组统计数量

业务: 有一个订单表,里面有个部门字段和订单状态的字段,现在需要根据部门分组统计不同订单状态的数量

表结构

订单状态是字典 1 待审核 2 待发货 3 已发货 4 已签收

DROP TABLE
IF
	EXISTS order_info;
CREATE TABLE order_info (
	`order_id` INT NOT NULL AUTO_INCREMENT COMMENT '订单号',
	`order_status` VARCHAR ( 32 ) DEFAULT '' COMMENT '订单状态',
	`dept` VARCHAR ( 32 ) DEFAULT '' COMMENT '部门',
	PRIMARY KEY ( order_id )
) COMMENT = '订单信息表';

INSERT INTO `my-tool`.`order_info` (`order_id`, `order_status`, `dept`) VALUES (null, '1', 'A部门');
INSERT INTO `my-tool`.`order_info` (`order_id`, `order_status`, `dept`) VALUES (null, '4', 'C部门');
INSERT INTO `my-tool`.`order_info` (`order_id`, `order_status`, `dept`) VALUES (null, '2', 'A部门');
INSERT INTO `my-tool`.`order_info` (`order_id`, `order_status`, `dept`) VALUES (null, '2', 'A部门');
INSERT INTO `my-tool`.`order_info` (`order_id`, `order_status`, `dept`) VALUES (null, '3', 'A部门');
INSERT INTO `my-tool`.`order_info` (`order_id`, `order_status`, `dept`) VALUES (null, '4', 'A部门');
INSERT INTO `my-tool`.`order_info` (`order_id`, `order_status`, `dept`) VALUES (null, '1', 'B部门');
INSERT INTO `my-tool`.`order_info` (`order_id`, `order_status`, `dept`) VALUES (null, '2', 'B部门');
INSERT INTO `my-tool`.`order_info` (`order_id`, `order_status`, `dept`) VALUES (null, '2', 'C部门');
INSERT INTO `my-tool`.`order_info` (`order_id`, `order_status`, `dept`) VALUES (null, '1', 'C部门');

查询sql

思路: 先按部门分组 查询同一个状态的数量 ,然后结果拼接 ,再竖表转横表

SELECT
	a.dept AS '部门',
	max( CASE a.order_status WHEN '1' THEN a.status_count ELSE 0 END ) AS '待审核',
	max( CASE a.order_status WHEN '2' THEN a.status_count ELSE 0 END ) AS '待发货',
	max( CASE a.order_status WHEN '3' THEN a.status_count ELSE 0 END ) AS '已发货',
	max( CASE a.order_status WHEN '4' THEN a.status_count ELSE 0 END ) AS '已签收' 
FROM
	(
	SELECT
		IFNULL( order_status, '1' ) order_status,
		COUNT( order_status ) status_count,
		dept 
	FROM
		order_info 
	WHERE
		order_status = '1' 
	GROUP BY
		dept UNION ALL
	SELECT
		IFNULL( order_status, '2' ) order_status,
		COUNT( order_status ) status_count,
		dept 
	FROM
		order_info 
	WHERE
		order_status = '2' 
	GROUP BY
		dept UNION ALL
	SELECT
		IFNULL( order_status, '3' ) order_status,
		COUNT( order_status ) status_count,
		dept 
	FROM
		order_info 
	WHERE
		order_status = '3' 
	GROUP BY
		dept UNION ALL
	SELECT
		IFNULL( order_status, '4' ) order_status,
		COUNT( order_status ) status_count,
		dept 
	FROM
		order_info 
	WHERE
		order_status = '4' 
	GROUP BY
		dept 
	) a
GROUP BY
	a.dept

结果