MySQL 查询按列的最后三个字符排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34832734/
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
Query to order by the last three characters of a column
提问by Sonam Kapoor
Query the Name of any student in STUDENTS who scored higher than 75 Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: "Bobby", "Robby", etc.), secondary sort them by ascending ID.
查询学生中得分高于 75 分的任何学生的姓名。按每个名称的最后三个字符对您的输出进行排序。如果两个或多个学生的姓名都以相同的最后三个字符结尾（即：“Bobby”、“Robby”等），则按升序对他们进行二次排序。
STUDENTS table has following columns:
STUDENTS 表具有以下列：
ID , NAME , MARKS
ID , NAME , MARKS
Sample input:
样本输入：
id name marks
1 ashley 81
2 samantha 75
3 julia 76
4 belvet 84
Sample Output:
示例输出：
Ashley
Julia
Belvet
Explanation:
解释：
Only Ashley, Julia, and Belvet have Marks > 75. If you look at the last three characters of each of their names, there are no duplicates and 'ley' < 'lia' < 'vet'.
只有 Ashley、Julia 和 Belvet 拥有Marks > 75. 如果您查看每个名字的最后三个字符，就会发现没有重复的 和'ley' < 'lia' < 'vet'。
this is correct output:
这是正确的输出：
select name from students where marks>75
order by substr(name,-3,3),id;
从分数> 75的学生中选择姓名
按 substr(name,-3,3),id 排序；
回答by hizbul25
Try with this for MySQL:
试试这个用于 MySQL：
SELECT NAME FROM STUDENTS WHERE Marks > 75 ORDER BY RIGHT(NAME, 3), ID ASC;
回答by Rohan Khude
If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID. Thats why ORDER BY IDis needed
如果两个或多个学生的姓名都以相同的最后三个字符结尾（即：Bobby、Robby 等），则按升序对他们进行二次排序。这就是为什么ORDER BY ID需要
For Mysql
对于 MySQL
SELECT Name FROM STUDENTS WHERE Marks>75 ORDER By SUBSTRING(Name,-3,LENGTH(Name)),ID
ref:-http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php
参考：- http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php
For Oracle
甲骨文
SELECT Name FROM Students WHERE Marks>75 ORDER BY substr(Name,-3),ID;
回答by TonyB
You can try this command:
你可以试试这个命令：
SELECT * FROM students ORDER BY RIGHT(name, 3), ID
Let me know. ;-).
让我知道。;-)
回答by Oleksandr Diachuk
DISTINCTshould be removed, otherwise multiple students with the same NAMEwould be ignored.
DISTINCT应该删除，否则多个相同的学生NAME将被忽略。
SELECT NAME
FROM STUDENTS
WHERE MARKS > 75
ORDER BY SUBSTRING(NAME, LEN(NAME)-2, 3), ID;
回答by Rishabh Shivhare
Best one is like this
最好的一个是这样的
Query for Output
查询输出
select name from students where marks>75 ORDER BY substr(name,-3) asc , id asc;
回答by Dharmil Thakkar
You can try below query:
您可以尝试以下查询：
SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY SUBSTRING(NAME,-3), ID;
NOTE: This one will work for MySQL
注意：这将适用于 MySQL
回答by Praveen Kumar C
Kindly try the below one,
请尝试以下一种，
SELECT Name FROM STUDENTS WHERE Marks >75 ORDER BY substr(name,-3,3),Id;
回答by sach
select name from students where marks > 75 order by right(name,3),ID;
从分数 > 75 的学生中选择姓名，按 right(name,3),ID 排序；
回答by Aashish Chaubey
It could be this using SUBSTRfunction in mysql
这可能是SUBSTR在mysql中使用函数
SELECT `name`
FROM `students`
WHERE `marks` > 75
ORDER BY SUBSTR(`name`, -3), ID ASC;
SUBSTR(name, -3)this will select the last 3 characters in the name column of the student table
SUBSTR(name, -3)这将选择学生表的名称列中的最后 3 个字符
回答by Rahul Vedanta
In case of Oracle
如果是甲骨文
SELECT NAME
FROM STUDENT
WHERE MARKS>75
ORDER BY SUBSTR(NAME,-3),ID;
