# Write your MySQL query statement below
2159. 分别排序两列
表：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 | +-----------+------------+
原站题解
mysql 解法, 执行用时: 285 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 22:55:46
# Write your MySQL query statement below select a.first_col, b.second_col from (select row_number() over(order by first_col) as id, first_col from Data) a join (select row_number() over(order by second_col desc) as id, second_col from Data) b on a.id = b.id
mysql 解法, 执行用时: 501 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 22:55:37
WITH
t1 AS
(SELECT
first_col,
ROW_NUMBER() OVER(ORDER BY first_col) AS rk
FROM
Data)
,
t2 AS
(SELECT
second_col,
ROW_NUMBER() OVER(ORDER BY second_col DESC) AS rk
FROM
Data)
SELECT
t1.first_col,
t2.second_col
FROM
t1
JOIN
t2
USING(rk);