mysql 行转列 case when 和聚合函数实现

8号看台

已于 2024-05-19 09:34:51 修改

阅读量132

点赞数 2

于 2024-05-14 16:21:31 首次发布

本文链接：https://blog.csdn.net/riding_horse/article/details/138859214

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一、原始数据

在这里插入图片描述

二、行转列

第一步

SELECT
	CASE WHEN SUBSTRING( aaa.cost_code, 1, 3 ) = '100' THEN aaa.budget_cost_budget ELSE 0 END  b1,
	CASE WHEN SUBSTRING( aaa.cost_code, 1, 3 ) = '300' THEN aaa.budget_cost_budget ELSE 0 END  b2,
	CASE WHEN SUBSTRING( aaa.cost_code, 1, 3 ) = '200' THEN aaa.budget_cost_budget ELSE 0 END  b3,
	CASE WHEN SUBSTRING( aaa.cost_code, 1, 3 ) = '400' THEN aaa.budget_cost_budget ELSE 0 END  b4,
	CASE WHEN SUBSTRING( aaa.cost_code, 1, 3 ) = '500' THEN aaa.budget_cost_budget ELSE 0 END  b5 
FROM
	ffff_info aaa 
WHERE
	aaa.budget_dh = 1 
	AND aaa.project_id = '04af0af9-c96d-4744-92d5-3967975b9f6e'

在这里插入图片描述

第二步

SELECT
	max( CASE WHEN SUBSTRING( aaa.cost_code, 1, 3 ) = '100' THEN aaa.budget_cost_budget ELSE 0 END ) b1,
	max( CASE WHEN SUBSTRING( aaa.cost_code, 1, 3 ) = '300' THEN aaa.budget_cost_budget ELSE 0 END ) b2,
	max( CASE WHEN SUBSTRING( aaa.cost_code, 1, 3 ) = '200' THEN aaa.budget_cost_budget ELSE 0 END ) b3,
	max( CASE WHEN SUBSTRING( aaa.cost_code, 1, 3 ) = '400' THEN aaa.budget_cost_budget ELSE 0 END ) b4,
	max( CASE WHEN SUBSTRING( aaa.cost_code, 1, 3 ) = '500' THEN aaa.budget_cost_budget ELSE 0 END ) b5 
FROM
	ffff_info aaa 
WHERE
	aaa.budget_dh = 1 
	AND aaa.project_id = '04af0af9-c96d-4744-92d5-3967975b9f6e' 
GROUP BY
	aaa.project_id