MySQL按条件计数

米兰@de小铁匠

已于 2022-10-19 10:19:36 修改

阅读量1.1k

点赞数 1

分类专栏：数据库文章标签： sql 数据库

于 2022-10-19 10:18:00 首次发布

本文链接：https://blog.csdn.net/weixin_43359484/article/details/127402281

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

原表结构为：

select * from k_bi_1;

在这里插入图片描述
按类别统计各个状态下的数量

-- 汇总
SELECT 
	mc1_mark AS 类别,
	SUM(1) AS 应入库数,
	SUM(IF(kp = 1, 1, 0)) AS 已开料数,
	SUM(IF(dp = 1, 1, 0)) AS 已打孔数,
	SUM(IF(fp = 1, 1, 0)) AS 已扫描数,
	SUM(IF(wh = 1, 1, 0)) AS 已入库数
FROM k_bi_1
	GROUP BY mc1_mark

在这里插入图片描述
需要将列转为行，期望效果如下：

实现方法：多个查询结果合并

SELECT 'G' AS flag,'应入库' AS indicator,COUNT(*) AS count FROM k_bi_1 WHERE mc1_mark = 'G'
UNION ALL
SELECT 'G' AS flag,'已开料' AS indicator,COUNT(*) AS count FROM k_bi_1 WHERE mc1_mark = 'G' AND kp = 1
UNION ALL
SELECT 'G' AS flag,'已打孔' AS indicator,COUNT(*) AS count FROM k_bi_1 WHERE mc1_mark = 'G' AND dp = 1
UNION ALL
SELECT 'G' AS flag,'已扫描' AS indicator,COUNT(*) AS count FROM k_bi_1 WHERE mc1_mark = 'G' AND fp = 1
UNION ALL
SELECT 'G' AS flag,'已入库' AS indicator,COUNT(*) AS count FROM k_bi_1 WHERE mc1_mark = 'G' AND wh = 1