主页 / coding / 问题 / 79201036
Accepted
Christian Bongiorno
Asked: 2024-11-19 02:29:34 +0800 CST

在 Oracle 中：如何按列值按组/群集排序

  • 772

这是我的数据样本

select * from (
    select 'A' as POD, 164 as result from dual union all
    select 'A' as POD, 3 as result from dual union all
    select 'A' as POD, 2 as result from dual union all
    select 'B' as POD, 409 as result from dual union all
    select 'B' as POD, 128 as result from dual union all
    select 'B' as POD, 5 as result from dual union all
    select 'C' as POD, 12391 as result from dual union all
    select 'C' as POD, 624 as result from dual union all
    select 'C' as POD, 405 as result from dual union all
    select 'C' as POD, 26 as result from dual union all
    select 'C' as POD, 3 as result from dual union all
    select 'C' as POD, 2 as result from dual
)
荚 结果
一个 164
一个 3
一个 2
乙 409
乙 128
乙 5
碳 12391
碳 624
碳 405
碳 二十六

我想要的是按照首次计数最高的组对它们进行排序：

荚 结果
碳 12391
碳 624
碳 405
碳 二十六
乙 409
乙 128
乙 5
一个 164
一个 3
一个 2

我甚至不知道如何用 SQL 来表达这个

Cresult在第一行中具有最高值，然后B具有第二高值A

sql

2 个回答

  1. Best Answer

    我理解这个任务如下：

    1. 查看 pod 的结果。首先显示结果最高的 pod，然后显示结果第二高的 pod，依此类推。
    2. 按结果降序排列每个 pod 中的行。

    现在，我们认为最高的结果是什么？这是每个 pod 的最大结果吗？那将是 A = 164，B = 409，C = 12391。我们可以用

    MAX(result) OVER (PARTITION BY pod)

    或者它是每个 pod 的最大结果总和？那将是 A = 169，B = 542，C = 13446。我们需要的表达式是

    SUM(result) OVER (PARTITION BY pod)

    在你的子句中使用其中一个ORDER BY。例如：

    SELECT pod, result
FROM mytable
ORDER BY
  MAX(result) OVER (PARTITION BY pod) DESC,
  pod,
  result DESC;
    • 3

  2. 另一种方法是使用 row_number 按以下方式对数据进行排序：

    select * from (
    select 'A' as POD, 164 as result from dual union all
    select 'A' as POD, 3 as result from dual union all
    select 'A' as POD, 2 as result from dual union all
    select 'B' as POD, 409 as result from dual union all
    select 'B' as POD, 128 as result from dual union all
    select 'B' as POD, 5 as result from dual union all
    select 'C' as POD, 12391 as result from dual union all
    select 'C' as POD, 624 as result from dual union all
    select 'C' as POD, 405 as result from dual union all
    select 'C' as POD, 26 as result from dual union all
    select 'C' as POD, 3 as result from dual union all
    select 'C' as POD, 2 as result from dual
)
order by pod desc, row_number()over(partition by pod order by result desc);

    希望有帮助。

    • 1

相关问题