如果指定了SELECT DISTINCT，则ORDER BY项必须出现在选择列表中。 [英] ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

问题描述

这很好用

  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屋！