查询按列的最后三个字符排序 [英] Query to order by the last three characters of a column
问题描述
查询 STUDENTS 中任何得分高于 75 分的学生的姓名.按每个名称的最后三个字符对您的输出进行排序.如果两个或多个学生的姓名都以相同的最后三个字符结尾(即:Bobby"、Robby"等),则按 ID 升序对他们进行二次排序.
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.
STUDENTS 表有以下列:
STUDENTS table has following columns:
ID、NAME、MARKS
样本输入:
id name marks
1 ashley 81
2 samantha 75
3 julia 76
4 belvet 84
示例输出:
Ashley
Julia
Belvet
说明:
只有 Ashley、Julia 和 Belvet 有 标记 >75
.如果您查看每个名字的最后三个字符,就会发现没有重复,并且 'ley' <'利亚' <'vet'
.
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'
.
这是正确的输出:
从学生中选择姓名>75
select name from students where marks>75
按 substr(name, -3, 3), id 排序;
order by substr(name, -3, 3), id;
推荐答案
在 MySQL 中试试这个:
Try with this for MySQL:
SELECT NAME FROM STUDENTS WHERE Marks > 75 ORDER BY RIGHT(NAME, 3), ID ASC;
这篇关于查询按列的最后三个字符排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!