表:Data
Column Name | Type |
---|---|
first_col | int |
second_col | int |
编写解决方案,使:
first_col 按照 升序 排列。
second_col 按照 降序 排列。
返回的结果格式如下。
示例 1:
输入:
Data 表:
first_col | second_col |
---|---|
4 | 2 |
2 | 3 |
3 | 1 |
1 | 4 |
输出:
first_col | second_col |
---|---|
1 | 4 |
2 | 3 |
3 | 2 |
4 | 1 |
细看里面是得分别对 first_col 和 second_col 进行排序,所以不是直接查询后进行排序就行,所以就是意味着是要查询出两个无关的 SQL,要如何连接呢,可以使用 row_number 窗口函数,这个是根据你指定的某个排序顺序,按顺序输出 1-2-3…可以用来做两个无关表的连表用,这样只要两个 SQL 的输出数量是一样的,就能刚好拼成多列的效果。
SELECT row_number() over (order by first_col) AS id, first_col
FROM Data
| id | first_col |
| -- | --------- |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
SELECT row_number() over (order by second_col desc) AS id, second_col
FROM Data
| id | second_col |
| -- | ---------- |
| 1 | 4 |
| 2 | 3 |
| 3 | 2 |
| 4 | 1 |
SELECT one.first_col, two.second_col
FROM (
SELECT row_number() over (order by first_col) AS id, first_col
FROM Data
) AS one
LEFT JOIN (
SELECT row_number() over (order by second_col desc) AS id, second_col
FROM Data
) AS two ON one.id = two.id