MySQL动态透视表无空值/空格问题及存储过程语法修复求助

一、修复LIKE CONCAT('%', this_zone, '%')的语法错误 #

在MySQL存储过程的动态SQL拼接中，引号嵌套处理不当是常见语法错误根源。以下是修正后的存储过程核心逻辑：

DELIMITER //
CREATE PROCEDURE Pivot2()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE this_zone VARCHAR(255);
    DECLARE zone_cursor CURSOR FOR SELECT DISTINCT zonename FROM your_table WHERE zonename IS NOT NULL AND zonename != '';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    SET @sql = 'SELECT ';
    
    OPEN zone_cursor;
    read_loop: LOOP
        FETCH zone_cursor INTO this_zone;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 关键修正：用双单引号转义存储过程字符串中的单引号，确保生成的SQL语法合法
        SET @sql = CONCAT(@sql, 'MAX(CASE WHEN zonename LIKE CONCAT(''%'', ''', this_zone, ''', ''%'') THEN asset_name END) AS `', this_zone, '`, ');
    END LOOP;
    CLOSE zone_cursor;
    
    -- 清理SQL末尾多余逗号，添加行号分组逻辑
    SET @sql = LEFT(@sql, LENGTH(@sql) - 2);
    SET @sql = CONCAT(@sql, ' FROM (SELECT asset_name, zonename, ROW_NUMBER() OVER (PARTITION BY zonename ORDER BY asset_name) AS row_num FROM your_table WHERE asset_name IS NOT NULL AND asset_name != '') AS temp GROUP BY row_num');
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

修正说明：

• 拼接CONCAT(''%'', ''', this_zone, ''', ''%'')时，通过双单引号转义存储过程字符串中的单引号，保证最终生成的SQL语句里的单引号格式正确。
• 避免裸写变量，通过CONCAT严格控制字符串拼接逻辑。

二、实现无空值的合并表视图（非空值上移） #

上面的存储过程已通过行号分组解决空值问题：

1. 子查询中用ROW_NUMBER() OVER (PARTITION BY zonename ORDER BY asset_name)为每个分区的资产名称生成独立行号。
2. 外层按行号分组，用MAX()聚合每个分区的资产名称，使每个行号对应的行中，各分区列仅保留非空值，自动消除空值/空格。

如果需要通过JavaScript传参调用（比如指定特定分区），可修改存储过程添加参数：

DELIMITER //
CREATE PROCEDURE Pivot2(IN zone_list VARCHAR(1000))
BEGIN
    SET @sql = 'SELECT ';
    
    -- 按传入的分区列表生成透视列（示例用FIND_IN_SET判断分区）
    SET @sql = CONCAT(@sql, 'MAX(CASE WHEN FIND_IN_SET(zonename, ''', zone_list, ''') THEN asset_name END) AS `', this_zone, '`, ');
    
    SET @sql = LEFT(@sql, LENGTH(@sql) - 2);
    SET @sql = CONCAT(@sql, ' FROM (SELECT asset_name, zonename, ROW_NUMBER() OVER (PARTITION BY zonename ORDER BY asset_name) AS row_num FROM your_table WHERE asset_name IS NOT NULL AND asset_name != '') AS temp GROUP BY row_num');
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

JavaScript调用示例：

const mysql = require('mysql2/promise');
async function callPivot() {
    const connection = await mysql.createConnection({host: 'localhost', user: 'your_user', password: 'your_pass', database: 'your_db'});
    // 获取去重分区列表
    const [rows] = await connection.execute('SELECT DISTINCT zonename FROM your_table WHERE zonename IS NOT NULL AND zonename != ""');
    const zoneList = rows.map(row => row.zonename).join(',');
    // 调用存储过程
    const [result] = await connection.execute('CALL Pivot2(?)', [zoneList]);
    console.log(result);
    await connection.end();
}
callPivot();

三、兼容低版本MySQL（无窗口函数） #

若MySQL版本低于8.0，不支持ROW_NUMBER()，可用变量模拟行号：

SELECT asset_name, zonename, 
       @row_num := IF(@current_zone = zonename, @row_num + 1, 1) AS row_num,
       @current_zone := zonename
FROM your_table, (SELECT @row_num := 0, @current_zone := '') AS temp
WHERE asset_name IS NOT NULL AND asset_name != ''
ORDER BY zonename, asset_name;

内容的提问来源于stack exchange，提问作者Ralph