问题:
I have a table e.g students:
id | name
-- |-----
1 | A
2 | B
3 | C
4 | D
5 | E
6 | F
7 | G
8 | H`
How to wite a sql in mysql so that first 4 students...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
I have a table e.g students:
id | name
-- |-----
1 | A
2 | B
3 | C
4 | D
5 | E
6 | F
7 | G
8 | H`
How to wite a sql in mysql so that first 4 students are order by id ascending and rest order by id descending output should be something like this
id | name
---|------
1 | A
2 | B
3 | C
4 | D
8 | H
7 | G
6 | F
5 | E
回答1:
You are using are version of MySQL earlier than 8+, which means that you can't use analytic functions here. Here is one way of doing this, using a correlated subquery with a CASE
expression:
SELECT t1.id, t1.name
FROM yourTable t1
ORDER BY
CASE WHEN (SELECT COUNT(*) FROM yourTable t2 WHERE t2.id <= t1.id) <= 4
THEN 0
ELSE 1 END,
CASE WHEN (SELECT COUNT(*) FROM yourTable t2 WHERE t2.id <= t1.id) <= 4
THEN id
ELSE -id END;
The reason I am calculating the row number above with a correlated subquery, rather than just using the id
, is that perhaps your id
values may not always start at 1
, or even be contiguous. Row number always can correctly target the first row records, as ordered by the id
, while the id
value itself may not always suffice.
Note that analytic functions make the code a lot easier to read here. Here is what you can do with MySQL 8+:
SELECT t1.id, t1.name
FROM yourTable t1
ORDER BY
CASE WHEN ROW_NUMBER() OVER (ORDER BY id) <= 4 THEN 0 ELSE 1 END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY id) <= 4 THEN id ELSE -id END;
回答2:
You can use a correlated sub query to calculate the "rank" of the row. Sorting is trivial:
SELECT *, (SELECT COUNT(*) FROM t AS x WHERE id < t.id) AS rn
FROM t
ORDER BY CASE WHEN rn >= 4 THEN -rn END, rn
SELECT COUNT(*) FROM t AS x WHERE id < t.id
assigns consecutive numbers to each row starting from 0
CASE WHEN rn >= 4 THEN -rn END
assigns NULL to rank number 0 ... 3 so secondary sort is required.
回答3:
Based on your sample data, the simplest method would be:
order by (case when id <= 4 then 1 else 2 end),
(case when id <= 4 then id end),
name desc;
If you don't know where the fourth id is, then:
select s.*
from students s cross join
(select s2.id
from students s2
order by s2.id
limit 3, 1
) as s4
order by (case when s.id <= s4.id then 1 else 2 end),
(case when s.id <= s4.id then s.id end),
s.name desc;