如何使用SQL按uploadstatus状态将记录拆分到两列分组展示

核心思路 #

要实现不同上传状态的实验室名称分两列并排展示，不能直接用普通分组聚合：由于两类状态的实验室数量不对等，需要先分别给「未上传」「已上传」的实验室列表生成从1开始的连续行号，再通过行号做关联对齐，即可得到你需要的两列结构，无对应值的位置自动留空。

通用实现代码（支持所有带窗口函数的数据库） #

WITH base_data AS (
    -- 复用原有查询的核心逻辑
    SELECT 
        l.id,
        l.facility_name as labName,
        CASE
            WHEN u.upload_status is NULL THEN '0'
            WHEN u.upload_status = '1' THEN '1'
        END AS upload_status
    FROM labs l
    LEFT JOIN upload u ON u.lab_name = l.facility_name
    INNER JOIN districts d ON d.id = l.district_code
),
not_uploaded AS (
    -- 筛选未上传实验室，生成连续行号
    SELECT
        labName,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM base_data
    WHERE upload_status = '0'
),
uploaded AS (
    -- 筛选已上传实验室，生成连续行号
    SELECT
        labName,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM base_data
    WHERE upload_status = '1'
)
-- 按行号对齐合并两列
SELECT
    n.labName AS `Not Uploaded`,
    u.labName AS `Uploaded`
FROM not_uploaded n
LEFT JOIN uploaded u ON n.rn = u.rn

注意事项 #

• 代码中ORDER BY id和你原查询的结果排序保持一致，如需调整展示顺序，直接修改OVER子句中的排序字段即可。
• 上述写法兼容MySQL 8.0+、PostgreSQL、SQL Server、Oracle等主流数据库版本，返回结果和你给出的期望结构完全匹配。
• 如果你使用的是不支持CTE和窗口函数的MySQL 5.x老版本，可以通过用户变量模拟行号实现，代码如下：

SELECT
    n.labName AS `Not Uploaded`,
    u.labName AS `Uploaded`
FROM (
    SELECT labName, @rn1 := @rn1 +1 AS rn
    FROM (
        SELECT l.id, l.facility_name AS labName
        FROM labs l
        LEFT JOIN upload u ON u.lab_name = l.facility_name
        INNER JOIN districts d ON d.id = l.district_code
        WHERE u.upload_status IS NULL
        ORDER BY l.id
    ) t1, (SELECT @rn1 :=0) r1
) n
LEFT JOIN (
    SELECT labName, @rn2 := @rn2 +1 AS rn
    FROM (
        SELECT l.id, l.facility_name AS labName
        FROM labs l
        LEFT JOIN upload u ON u.lab_name = l.facility_name
        INNER JOIN districts d ON d.id = l.district_code
        WHERE u.upload_status = '1'
        ORDER BY l.id
    ) t2, (SELECT @rn2 :=0) r2
) u ON n.rn = u.rn

• 如果后续已上传的实验室数量超过未上传的数量，可以将左连接替换为全外连接，避免丢失已上传列表的尾部记录（MySQL不支持全外连接，可通过UNION ALL合并左右连接的结果实现）。

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