MySQL移除空值并实现单元格上移的解决方案咨询

问题说明 #

创建了名为semana的表，包含seg和ter两个字段，插入数据后查询结果存在空值，需要移除空值并将非空单元格上移——让seg列的非空值位于首行，ter列的非空值依次填充空行位置。

原表创建与插入代码 #

-- 创建表
CREATE TABLE semana (
  seg VARCHAR(30),
  ter VARCHAR(30)
);

-- 插入数据
INSERT INTO semana VALUES ('aa', '');
INSERT INTO semana VALUES ('', 'tt');
INSERT INTO semana VALUES ('' , 'bb');

当前查询的问题 #

原查询语句：

SELECT seg, ter FROM semana ORDER BY semana.seg, ter

返回结果包含空值，无法实现非空单元格上移的需求。

解决方案 #

可以通过窗口函数分别为seg和ter的非空值生成行号，再通过行号关联得到目标结果。以下是兼容MySQL 8.0+、PostgreSQL、SQL Server等主流数据库的实现代码：

WITH seg_data AS (
    SELECT seg, ROW_NUMBER() OVER (ORDER BY seg) AS rn
    FROM semana
    WHERE seg <> ''
),
ter_data AS (
    SELECT ter, ROW_NUMBER() OVER (ORDER BY ter) AS rn
    FROM semana
    WHERE ter <> ''
)
SELECT 
    seg_data.seg,
    ter_data.ter
FROM seg_data
RIGHT JOIN ter_data ON seg_data.rn = ter_data.rn
UNION ALL
SELECT 
    seg_data.seg,
    NULL AS ter
FROM seg_data
LEFT JOIN ter_data ON seg_data.rn = ter_data.rn
WHERE ter_data.rn IS NULL
ORDER BY rn;

代码解释 #

1. seg_data：提取seg列的非空值，按seg排序后生成行号。
2. ter_data：提取ter列的非空值，按ter排序后生成行号。
3. 通过RIGHT JOIN关联两个数据集，确保ter列的所有非空值都被保留；再用UNION ALL补充seg列中存在但ter列无对应行号的值。
4. 最终按行号排序，得到非空单元格上移的结果。

如果使用MySQL 5.x等不支持CTE的版本，可改用子查询实现：

SELECT 
    s.seg,
    t.ter
FROM (
    SELECT seg, @row1 := @row1 + 1 AS rn
    FROM semana, (SELECT @row1 := 0) AS init
    WHERE seg <> ''
    ORDER BY seg
) s
RIGHT JOIN (
    SELECT ter, @row2 := @row2 + 1 AS rn
    FROM semana, (SELECT @row2 := 0) AS init
    WHERE ter <> ''
    ORDER BY ter
) t ON s.rn = t.rn
UNION ALL
SELECT 
    s.seg,
    NULL AS ter
FROM (
    SELECT seg, @row3 := @row3 + 1 AS rn
    FROM semana, (SELECT @row3 := 0) AS init
    WHERE seg <> ''
    ORDER BY seg
) s
LEFT JOIN (
    SELECT ter, @row4 := @row4 + 1 AS rn
    FROM semana, (SELECT @row4 := 0) AS init
    WHERE ter <> ''
    ORDER BY ter
) t ON s.rn = t.rn
WHERE t.rn IS NULL
ORDER BY rn;

内容的提问来源于stack exchange，提问作者Djoi Patrick Santos de Souza