如何让SQL查询结果按IN子句指定顺序返回而非默认ID排序？

你需要让查询结果严格遵循IN子句中IDPEDV的指定顺序返回，默认排序逻辑（通常基于主键或索引）无法满足该需求，以下是针对主流数据库的实现方案：

MySQL/MariaDB #

使用FIELD()函数，它会返回目标值在参数列表中的位置，以此作为排序依据：

select CODART as 'MODELO / REF', DESCLIN as DESCRIPCION, UNIDADES as CANTIDAD 
from LINEPEDI 
where IDPEDV in ('69782','69780','68931','69781')
order by FIELD(IDPEDV, '69782','69780','68931','69781');

SQL Server #

方案1：CASE语句（兼容全版本） #

通过CASE为每个指定ID分配排序权重：

select CODART as 'MODELO / REF', DESCLIN as DESCRIPCION, UNIDADES as CANTIDAD 
from LINEPEDI 
where IDPEDV in ('69782','69780','68931','69781')
order by 
    CASE IDPEDV
        WHEN '69782' THEN 1
        WHEN '69780' THEN 2
        WHEN '68931' THEN 3
        WHEN '69781' THEN 4
    END;

方案2：STRING_SPLIT（2016及以上版本） #

借助STRING_SPLIT的序号字段实现排序：

SELECT l.CODART as 'MODELO / REF', l.DESCLIN as DESCRIPCION, l.UNIDADES as CANTIDAD
FROM LINEPEDI l
JOIN STRING_SPLIT('69782,69780,68931,69781', ',') s ON l.IDPEDV = s.value
ORDER BY s.ordinal;

PostgreSQL #

利用array_position()函数，将目标ID转为数组后获取位置排序：

select CODART as "MODELO / REF", DESCLIN as DESCRIPCION, UNIDADES as CANTIDAD 
from LINEPEDI 
where IDPEDV in ('69782','69780','68931','69781')
order by array_position(array['69782','69780','68931','69781'], IDPEDV);

Oracle #

方案1：CASE语句 #

和SQL Server逻辑一致，通过自定义权重排序：

select CODART as "MODELO / REF", DESCLIN as DESCRIPCION, UNIDADES as CANTIDAD 
from LINEPEDI 
where IDPEDV in ('69782','69780','68931','69781')
order by 
    CASE IDPEDV
        WHEN '69782' THEN 1
        WHEN '69780' THEN 2
        WHEN '68931' THEN 3
        WHEN '69781' THEN 4
    END;

方案2：INSTR函数 #

通过匹配字符串中ID的位置实现排序：

select CODART as "MODELO / REF", DESCLIN as DESCRIPCION, UNIDADES as CANTIDAD 
from LINEPEDI 
where IDPEDV in ('69782','69780','68931','69781')
order by INSTR('69782,69780,68931,69781', IDPEDV);

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