SQL查询，百分比+%

最新推荐文章于 2023-10-27 14:51:34 发布

云逸@

最新推荐文章于 2023-10-27 14:51:34 发布

阅读量1.2k

点赞数

文章标签： sql java 数据库

本文链接：https://blog.csdn.net/weixin_42592992/article/details/123353680

版权

SELECT
	p.id,
	p.planName ,
	p.nodeUpdateTime,
	isnull( COUNT ( d.id ), 0 ) AS total,
	CONVERT(VARCHAR(20),COALESCE ( SUM ( CASE WHEN d.NODE_NAME = '预录入' THEN 1 ELSE 0 END ), 0 )*100/isnull( COUNT ( d.id ), 0 ))+'%' count1,
	COALESCE ( SUM ( CASE WHEN d.NODE_NAME = '预计划' THEN 1 ELSE 0 END ), 0 )*100/isnull( COUNT ( d.id ), 0 ) count2,
	COALESCE ( SUM ( CASE WHEN d.NODE_NAME = '录入' THEN 1 ELSE 0 END ), 0 )*100/isnull( COUNT ( d.id ), 0 ) count3,
	COALESCE ( SUM ( CASE WHEN d.NODE_NAME = '暂存' THEN 1 ELSE 0 END ), 0 )*100/isnull( COUNT ( d.id ), 0 ) count4,
	COALESCE ( SUM ( CASE WHEN d.NODE_NAME = '发布' THEN 1 ELSE 0 END ), 0 )*100/isnull( COUNT ( d.id ), 0 ) count5,
	COALESCE ( SUM ( CASE WHEN d.NODE_NAME = '退回' THEN 1 ELSE 0 END ), 0 )*100/isnull( COUNT ( d.id ), 0 ) count6,
	COALESCE ( SUM ( CASE WHEN d.NODE_NAME = '接收' THEN 1 ELSE 0 END ), 0 )*100/isnull( COUNT ( d.id ), 0 ) count7,
	COALESCE ( SUM ( CASE WHEN d.NODE_NAME = '申请退回' THEN 1 ELSE 0 END ), 0 )*100/isnull( COUNT ( d.id ), 0 ) count8,
	COALESCE ( SUM ( CASE WHEN d.NODE_NAME = '修复' THEN 1 ELSE 0 END ), 0 )*100/isnull( COUNT ( d.id ), 0 ) count9,
	COALESCE ( SUM ( CASE WHEN d.NODE_NAME = '合格' THEN 1 ELSE 0 END ), 0 )*100/isnull( COUNT ( d.id ), 0 ) count10,
	COALESCE ( SUM ( CASE WHEN d.NODE_NAME = '不合格' THEN 1 ELSE 0 END ), 0 )*100/isnull( COUNT ( d.id ), 0 ) count11 
FROM
	X_RepairProject p
	LEFT JOIN Damage d ON p.id= d.xxProjectId 
WHERE
	p.isDelete= 0 
	AND p.new_node_code= 'xx01' 
	AND p.id = '202111091317152912000DED3B7BBAB3' 
GROUP BY
	p.id,
	p.planName,
	p.nodeUpdateTime

在这里插入图片描述