SQL 如果指定了 SELECT DISTINCT，则 ORDER BY 项目必须出现在选择列表中......我花了几个小时但没有找到
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15007282/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORDER BY items must appear in the select list if SELECT DISTINCT is specified..... I spents hours but nothing found
提问by Haider
Below is the part of my large sql query and I could not find a solution of my problem after spending hours on it. My requirement is only sort the records on RaceNumber ascending and put null records on bottom
下面是我的大型 sql 查询的一部分，花了几个小时后我找不到我的问题的解决方案。我的要求只是对 RaceNumber 上的记录进行升序排序，并将空记录放在底部
SELECT DISTINCT TP.racenumber,
TP.teamid,
TP.teamname
FROM tblteamprofile TP
ORDER BY CASE
WHEN TP.racenumber IS NULL THEN 1
ELSE 0
end,
TP.teamid,
TP.teamname
Can anybody help me it is only my humble request...! please
任何人都可以帮助我这只是我卑微的请求......！请
回答by mechanical_meat
Add the missing ORDER BY item to the SELECT list:
将缺少的 ORDER BY 项添加到 SELECT 列表：
SELECT DISTINCT
TP.RaceNumber,
CASE WHEN TP.RaceNumber is null THEN 1 ELSE 0 END,
TP.TeamID,
TP.TeamName
FROM
tblTeamProfile TP
ORDER BY
CASE WHEN TP.RaceNumber is null THEN 1 ELSE 0 END,
TP.TeamID,
TP.TeamName
As Factor Mystic astutely notes in the comments, you can simplify the query if your RDBMS allows re-using an alias in the ORDER BY clause:
正如 Factor Mystic 在评论中敏锐地指出的那样，如果您的 RDBMS 允许在 ORDER BY 子句中重用别名，您可以简化查询：
SELECT DISTINCT
TP.RaceNumber,
CASE WHEN TP.RaceNumber is null THEN 1 ELSE 0 END AS RaceNumberFlag,
TP.TeamID,
TP.TeamName
FROM
tblTeamProfile TP
ORDER BY
RaceNumberFlag,
TP.TeamID,
TP.TeamName
回答by lc.
The problem is the database engine doesn't know how to select out that particular value if it is not part of the SELECTlist in a SELECT DISTINCTquery.
问题是数据库引擎不知道如何选择该特定值，如果它不是查询SELECT列表的一部分SELECT DISTINCT。
You should be able to use a GROUP BYinstead of DISTINCTif you don't want to select the column:
如果您不想选择列，您应该可以使用 aGROUP BY而不是DISTINCT：
SELECT TP.racenumber, TP.teamid, TP.teamname
FROM tblteamprofile TP
GROUP BY TP.racenumber, TP.teamid, TP.teamname,
CASE WHEN TP.racenumber IS NULL THEN 1 ELSE 0 END
ORDER BY CASE WHEN TP.racenumber IS NULL THEN 1 ELSE 0 END,
TP.teamid, TP.teamname
回答by Gordon Linoff
Perhaps the order by should be this:
也许 order by 应该是这样的：
order by (case when tp.RaceNumber is NULL then 1 else 0 end),
tp.RaceNumber
If you want to order by RaceNumber, why do you have TeamId and TeamName in the query?
如果要按 RaceNumber 排序，为什么查询中有 TeamId 和 TeamName？
