MS SQL透视查询转MySQL透视查询技术求助

MySQL 并没有像 MS SQL 那样内置 PIVOT 函数，所以我们需要用 CASE 表达式 + 聚合函数 来模拟透视效果，同时调整动态 SQL 的写法适配 MySQL 的语法。下面是针对你的需求的具体实现：

1. 静态版本（对应原查询硬编码的 [Visit 1],[Visit 2]） #

如果你的访问组名称是固定的，可以直接写静态查询，逻辑和原 MS SQL 完全一致：

SELECT
    p.id,
    a.name,
    s.name AS serviceName,
    liv.subject_count,
    MAX(CASE WHEN vg.name = 'Visit 1' THEN v.quantity END) AS `Visit 1`,
    MAX(CASE WHEN vg.name = 'Visit 2' THEN v.quantity END) AS `Visit 2`
FROM protocols p
JOIN arms a ON p.id = a.protocol_id
JOIN visit_groups vg ON a.id = vg.arm_id
JOIN visits v ON vg.id = v.visit_group_id
JOIN line_items_visits liv ON a.id = liv.arm_id AND v.line_items_visit_id = liv.id
JOIN line_items li ON liv.line_item_id = li.id
JOIN services s ON li.service_id = s.id
WHERE p.id = '591' AND a.id = '377'
GROUP BY p.id, a.name, s.name, liv.subject_count;

2. 动态版本（自动生成访问组列，适配动态场景） #

如果访问组名称是动态变化的，我们可以用 MySQL 的动态 SQL 语法（PREPARE + EXECUTE）来实现，和原 MS SQL 的动态逻辑对应：

-- 第一步：动态生成透视列的 CASE 语句和列名列表
SET @cols = NULL;
SET @case_cols = NULL;

SELECT
    GROUP_CONCAT(DISTINCT CONCAT('`', vg.name, '`') SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN vg.name = ''', vg.name, ''' THEN v.quantity END) AS `', vg.name, '`') SEPARATOR ', ')
INTO @cols, @case_cols
FROM visit_groups vg
JOIN arms a ON vg.arm_id = a.id
WHERE a.id = '377' AND a.protocol_id = '591';

-- 第二步：拼接完整的查询语句
SET @query = CONCAT(
    'SELECT p.id, a.name, s.name AS serviceName, liv.subject_count, ',
    @case_cols,
    ' FROM protocols p
    JOIN arms a ON p.id = a.protocol_id
    JOIN visit_groups vg ON a.id = vg.arm_id
    JOIN visits v ON vg.id = v.visit_group_id
    JOIN line_items_visits liv ON a.id = liv.arm_id AND v.line_items_visit_id = liv.id
    JOIN line_items li ON liv.line_item_id = li.id
    JOIN services s ON li.service_id = s.id
    WHERE p.id = ''591'' AND a.id = ''377''
    GROUP BY p.id, a.name, s.name, liv.subject_count'
);

-- 第三步：执行动态查询
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键差异说明 #

• MySQL 无内置 PIVOT，用 CASE + MAX() 模拟：原 MS SQL 的 PIVOT(max(quantity) for visitGroup in (...)) 等价于 MySQL 中对每个访问组写一个 MAX(CASE ...) 表达式。
• 动态 SQL 写法不同：MS SQL 用 DECLARE @query NVARCHAR(MAX) + EXECUTE(@query)，MySQL 用 SET 拼接字符串，再通过 PREPARE 和 EXECUTE 执行。
• 列名转义：MS SQL 用方括号 [] 转义带空格的列名，MySQL 用反引号 `。
• 关联语法优化：把原查询的旧风格逗号关联改成了显式 JOIN，更清晰且避免隐式笛卡尔积风险。

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