如果指定了SELECT DISTINCT,则ORDER BY项必须出现在选择列表中。 [英] ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
本文介绍了如果指定了SELECT DISTINCT,则ORDER BY项必须出现在选择列表中。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这很好用
SELECT DISTINCT SED.MCSED_SED_Short_Description,SCH.Doc_SNo,
FROM @ Schedule_Status SCH,DMS_M_Company_Schedule_Element_Details SED
WHERE SED.MCSED_SED_Code = 123
ORDER BY SED.MCSED_SED_Short_Description,SCH.Doc_SNo,SCH.Doc_No
但是,当我按顺序引入case表达式时,它显示错误
SELECT DISTINCT SED.MCSED_SED_Short_Description,SCH.Doc_SNo,
FROM @ Schedule_Status SCH,DMS_M_Company_Schedule_Element_Details SED
WHER E SED.MCSED_SED_Code = 123
ORDER BY CASE SED.MCSED_SED_Short_Description WHEN ' VD' THEN 1 ELSE 2 END ,
SCH.Doc_SNo,SCH.Doc_No
它显示错误如果指定了SELECT DISTINCT,则ORDER BY项必须出现在选择列表中。
解决方案
删除DISTINCT并添加GROUP BY子句
要解决此问题,只需将字段 SCH.Doc_No 添加到您的select子句中。
要了解有关此问题的更多信息,请阅读以下文章:
SQL Distinct&按问题排序 [ ^ ]
希望这会有所帮助。
SELECT DISTINCT SED.MCSED_SED_Short_Description,SCH.Doc_SNo,
ord = CASE SED.MCSED_SED_Short_Description WHEN'VD'THEN 1 ELSE 2 END,
SCH.Doc_No
FROM @Schedule_Status SCH, DMS_M_Company_Schedule_Element_Details SED
WHERE SED.MCSED_SED_Code = 123
ORDER BY CASE SED.MCSED_SED_Short_Description WHEN'VD'THEN 1 ELSE 2 END,
SCH.Doc_SNo,SCH.Doc_No
Hi,
This works fine
SELECT DISTINCT SED.MCSED_SED_Short_Description, SCH.Doc_SNo,
FROM @Schedule_Status SCH , DMS_M_Company_Schedule_Element_Details SED
WHERE SED.MCSED_SED_Code =123
ORDER BY SED.MCSED_SED_Short_Description, SCH.Doc_SNo, SCH.Doc_No
But when i introduce case expression in order by clause it shows the error
SELECT DISTINCT SED.MCSED_SED_Short_Description, SCH.Doc_SNo,
FROM @Schedule_Status SCH , DMS_M_Company_Schedule_Element_Details SED
WHERE SED.MCSED_SED_Code =123
ORDER BY CASE SED.MCSED_SED_Short_Description WHEN 'VD' THEN 1 ELSE 2 END,
SCH.Doc_SNo, SCH.Doc_No
it shows the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."
解决方案
Remove DISTINCT and add GROUP BY clause
To fix the issue just add the field SCH.Doc_No to your select clause.
To know more about this issue read the below article :
SQL Distinct & Order by Issue[^]
Hope this helps.
SELECT DISTINCT SED.MCSED_SED_Short_Description, SCH.Doc_SNo, ord = CASE SED.MCSED_SED_Short_Description WHEN 'VD' THEN 1 ELSE 2 END, SCH.Doc_No FROM @Schedule_Status SCH , DMS_M_Company_Schedule_Element_Details SED WHERE SED.MCSED_SED_Code =123 ORDER BY CASE SED.MCSED_SED_Short_Description WHEN 'VD' THEN 1 ELSE 2 END, SCH.Doc_SNo, SCH.Doc_No
这篇关于如果指定了SELECT DISTINCT,则ORDER BY项必须出现在选择列表中。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文