MySQL如何实现按patient_id展示多次到访记录的转置列查询？

要实现将患者多次就诊记录转为横向展示的需求，核心思路是先给每个患者的就诊记录按时间排序生成序号，再通过条件聚合将行数据转为列。以下是针对不同数据库环境的实现方式：

方式一：支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、SQL Server等） #

使用ROW_NUMBER()窗口函数为每个患者的就诊记录生成序号，再通过CASE WHEN配合聚合函数提取对应次数的就诊时间，缺失的记录用COALESCE替换为'x'：

SELECT
    patient_id,
    COALESCE(MAX(CASE WHEN visit_num = 1 THEN created_at END), 'x') AS `1st come`,
    COALESCE(MAX(CASE WHEN visit_num = 2 THEN created_at END), 'x') AS `2nd come`,
    COALESCE(MAX(CASE WHEN visit_num = 3 THEN created_at END), 'x') AS `3rd come`
FROM (
    SELECT 
        patient_id,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY created_at) AS visit_num
    FROM t_patients
) AS ranked_visits
GROUP BY patient_id
ORDER BY patient_id;

逻辑说明： #

1. 子查询ranked_visits：按patient_id分组，每组内按created_at升序排序，生成就诊次数的序号visit_num。
2. 外层查询：按patient_id分组，用CASE WHEN筛选对应序号的就诊时间，MAX聚合确保每个分组只返回一个值，COALESCE将无对应记录的NULL值替换为'x'。

方式二：不支持窗口函数的老版本MySQL #

通过用户变量手动生成就诊序号，核心逻辑与方式一一致：

SELECT
    patient_id,
    COALESCE(MAX(CASE WHEN visit_num = 1 THEN created_at END), 'x') AS `1st come`,
    COALESCE(MAX(CASE WHEN visit_num = 2 THEN created_at END), 'x') AS `2nd come`,
    COALESCE(MAX(CASE WHEN visit_num = 3 THEN created_at END), 'x') AS `3rd come`
FROM (
    SELECT 
        patient_id,
        created_at,
        @row_num := IF(@current_patient = patient_id, @row_num + 1, 1) AS visit_num,
        @current_patient := patient_id
    FROM t_patients, (SELECT @row_num := 0, @current_patient := NULL) AS vars
    ORDER BY patient_id, created_at
) AS ranked_visits
GROUP BY patient_id
ORDER BY patient_id;

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