主页 / dba / 问题 / 63950
Accepted
shomel
Asked: 2014-04-26 11:52:08 +0800 CST

在行内排序

  • 772

我想知道，是否有一种方法可以对每一行内的数据进行排序？例如，我有表

val1, 3, 2, 1
val2, 2, 3, 1
val3, 3, 1, 2
val4, 1, 3, 2
val5, 1, 2, 3

并希望选择它排序，因为

val1, 1, 2, 3
val2, 1, 2, 3
val3, 1, 2, 3
val4, 1, 2, 3
val5, 1, 2, 3
oracle sorting

1 个回答

  1. Best Answer

    好吧，请看下文。这是我绝对不会在真实环境中写下的东西（我会质疑原来的问题）。

    drop table t1;
create table t1 (val varchar2(5), c1 number, c2 number, c3 number);
insert into t1 values ('val1', 10, 2, 5);
insert into t1 values ('val2', 7, 3, 1);
insert into t1 values ('val3', 4, 4, 8);
insert into t1 values ('val4', 1, 9, 2);
insert into t1 values ('val5', 3, 5, 3);
insert into t1 values ('val6', 8, 2, 10);
commit;
select * from t1;

VAL           C1         C2         C3
----- ---------- ---------- ----------
val1          10          2          5
val2           7          3          1
val3           4          4          8
val4           1          9          2
val5           3          5          3
val6           8          2         10

    到目前为止一切顺利，只是示例数据。现在做一个 UNPIVOT，然后 PARTITION BY val，ORDER BY C 值，然后 PIVOT 回来：

    with t2 as (select * from t1 unpivot (c for cval in (c1 as 'A', c2 as 'B', c3 as 'C')) order by val, c),
     t3 as (select val, row_number() over (partition by val order by c) as rank, c from t2)
select * from t3 pivot (min(c) as c for rank in (1,2,3)) order by val;

VAL          1_C        2_C        3_C
----- ---------- ---------- ----------
val1           2          5         10
val2           1          3          7
val3           4          4          8
val4           1          2          9
val5           3          3          5
val6           2          8         10

    限制：

    • 需要为额外的列调整 SQL
    • 真的很难阅读和理解（在我看来）
    • 1

相关问题