利用存储过程，拼接SQL语句，实现行转列

最新推荐文章于 2024-09-19 14:20:30 发布

卫生球球

最新推荐文章于 2024-09-19 14:20:30 发布

阅读量2.1k

点赞数

本文链接：https://blog.csdn.net/z47z47z47/article/details/79877015

版权

某些时候需要将查询出来的行转成列来进行输出，就需要使用concat函数来拼接SQL语句，而在mybatis中，无法使用SET定义变量，所以要利用到存储过程。

DELIMITER &&
DROP PROCEDURE
IF EXISTS Qurey_Status;
CREATE PROCEDURE Qurey_Status() READS SQL DATA 
BEGIN

SET @SQL = NULL; --创建一条SQL语句
SELECT
    GROUP_CONCAT( DISTINCT CONCAT( 'MAX(IF(t1.alarm_netype = ''', t1.alarm_netype, ''', t2.level_name, 0)) AS ''', t1.alarm_netype, '\'' ) ) INTO @SQL 
FROM
    t_alarm_current t1
LEFT JOIN t_dim_alarm_level t2 ON t2.level_name = t1.alarm_level; --通过SELECT查询来动态拼接SQL语句

SET @SQL = CONCAT( 'SELECT t1.alarm_region, (SELECT level_name FROM t_dim_alarm_level WHERE level_num=MAX(t2.level_num)) status, ', @SQL, ' FROM t_alarm_current t1 
		    LEFT JOIN t_dim_alarm_level t2 ON t2.level_name = t1.alarm_level
		    GROUP BY t1.alarm_region order by status desc'); --根据需求拼接完整的SQL语句
PREPARE stmt 
FROM @SQL;  --自定义执行
EXECUTE stmt;  --执行
DEALLOCATE PREPARE stmt;  --删除

END && 
DELIMITER;