如何在MariaDB中按指定顺序返回包含空值的行

问题出在你用ORDER BY validade asc时，不存在的05.173对应的validade是null，SQL里null在升序排序时会排在最前面，直接打乱了原始列表的顺序。要保留原始顺序，你需要给子查询里的每条记录标记原始位置，最后按这个位置排序。

方案1：支持窗口函数的数据库（MySQL 8+、PostgreSQL、SQL Server等） #

给子查询a添加行号记录原始顺序，最后按行号排序：

SELECT b.*, a.sia_id
FROM (
    SELECT '02.352' AS sia_id, ROW_NUMBER() OVER () AS row_num
    UNION ALL
    SELECT '05.173' AS sia_id, ROW_NUMBER() OVER () AS row_num
    UNION ALL
    SELECT '02.352' AS sia_id, ROW_NUMBER() OVER () AS row_num
    UNION ALL
    SELECT '05.173' AS sia_id, ROW_NUMBER() OVER () AS row_num
    UNION ALL
    SELECT '05.451' AS sia_id, ROW_NUMBER() OVER () AS row_num
) AS a
LEFT JOIN estoques AS b
    USING (sia_id)
ORDER BY a.row_num ASC;

方案2：不支持窗口函数的旧版MySQL（如MySQL 5.x） #

用用户变量生成行号：

SELECT b.*, a.sia_id
FROM (
    SELECT @row_num := @row_num + 1 AS row_num, t.sia_id
    FROM (
        SELECT '02.352' AS sia_id
        UNION ALL
        SELECT '05.173' AS sia_id
        UNION ALL
        SELECT '02.352' AS sia_id
        UNION ALL
        SELECT '05.173' AS sia_id
        UNION ALL
        SELECT '05.451' AS sia_id
    ) AS t, (SELECT @row_num := 0) AS init
) AS a
LEFT JOIN estoques AS b
    USING (sia_id)
ORDER BY a.row_num ASC;

效果说明 #

修改后，查询结果会严格按照你子查询里sia_id的原始顺序返回：存在的记录显示对应数据，不存在的05.173会保留在它原本的位置上，输出结果完全符合你的预期。

内容的提问来源于stack exchange，提问作者marcelo.delta