将MySQL查询结果按区域名称列展示的实现问题

你当前存储过程的核心问题是：仅将拼接好的IF语句字符串存入@answers并直接作为字段查询，MySQL会把它当成文本输出，不会执行逻辑计算。要实现预期的行转列效果，必须用动态SQL拼接完整查询语句，再执行它。

修正后的存储过程实现 #

DELIMITER //

CREATE PROCEDURE GetAssetLocationPivot()
BEGIN
    -- 1. 动态生成列转换逻辑：每个区域对应一列，填充对应资产名称
    SELECT GROUP_CONCAT(
        DISTINCT CONCAT(
            "MAX(IF(zonename = '", zonename, "', name, NULL)) AS `", zonename, "`"
        )
    ) INTO @pivot_columns
    FROM node;

    -- 2. 拼接完整动态SQL：先取每个资产的最新位置，再行转列
    SET @sql = CONCAT(
        "SELECT ", @pivot_columns, "
        FROM (
            -- 子查询：获取每个资产的最新位置记录（确保取到当前最新的ts）
            SELECT 
                a.name, 
                n.zonename,
                ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY l.ts DESC) AS rn
            FROM logs l
            INNER JOIN asset a ON a.id = l.assetid
            INNER JOIN node n ON n.id = l.nodeid
            INNER JOIN zonegroup zg ON zg.id = n.zonegroupid
            INNER JOIN site s ON s.id = zg.siteid
            WHERE a.serial != 'Unassigned' 
              AND l.rssi >= threshold
        ) AS latest_locations
        WHERE rn = 1
        GROUP BY name"
    );

    -- 3. 执行动态SQL
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

关键细节说明 #

• 最新位置准确性：原查询的group by ts desc, name desc逻辑不严谨，改用ROW_NUMBER()窗口函数，给每个资产的日志按时间戳降序标记排名，取rn=1的记录，确保拿到资产当前的最新位置。
• 动态列生成：用GROUP_CONCAT拼接每个区域的MAX(IF(...))语句，资产属于该区域时显示名称，否则为NULL；MAX用于聚合结果，避免空值覆盖有效数据。
• 动态SQL执行：必须通过PREPARE和EXECUTE执行拼接好的SQL语句，而非直接输出字符串。

执行存储过程 #

CALL GetAssetLocationPivot();

执行后将得到你期望的格式：

Reception    Triage     XRay
fred         rachael    john
jack         NULL       susan

（若需要空列显示为空字符串而非NULL，可将MAX(IF(...))里的NULL替换为''）

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