“如果指定了SELECT DISTINCT，则ORDER BY项目必须出现在选择列表中”如何处理

在SQL查询中同时使用DISTINCT和ORDER BY

SELECT DISTINCT sbzrbm, sybm
FROM clsbtz
WHERE sbzrbm IS NOT NULL
GROUP BY sbzrbm
ORDER BY (CASE sbzrbm WHEN '一车间' THEN 1 WHEN '二车间' THEN 2 WHEN
'三车间' THEN 3 WHEN '四车间' THEN 4 WHEN '五车间' THEN
5 END)

SQL查询中能够同时使用DISTINCT和ORDER BY

这样写可以同时用

SELECT DISTINCT sbzrbm, sybm
FROM clsbtz
WHERE sbzrbm IS NOT NULL
ORDER BY (CASE sbzrbm WHEN '一车间' THEN 1 WHEN '二车间' THEN 2 WHEN
'三车间' THEN 3 WHEN '四车间' THEN 4 WHEN '五车间' THEN
5 END)

可以先进行select distinct，再进行order by。

参考

select t1.*
from (
    select distinct t1.username
    from users t1
) t1
left join users_log t2 on t1.username=t2.username
order by t2.create_time;

SELECT distinct (CASE sbzrbm WHEN '一车间' THEN 1 WHEN '二车间' THEN 2 WHEN
'三车间' THEN 3 WHEN '四车间' THEN 4 WHEN '五车间' THEN
5 END) as sbzrbm, sybm
FROM clsbtz
WHERE sbzrbm IS NOT NULL
ORDER BY sbzrbm