sql-server - 在 SQL Server 中对两列执行双重排序，并且主排序不在前两列的计算中

Question

我有一个使用 SQL Server 2008 的表，它有一个表，上面有两个可排序的列，一个是手动设置的，另一个是由系统过程计算的(此过程将所有内容作为一个整体进行排序，并指定从 10 开始的排序，直到最高行数乘以 10)

ID    Manual     System
------------------------
1      null      300
2      2         380
3      null      500
4      null      200

我试图让它将 id 排序为 4,2,1,3

我希望输出在应用后对系统进行手动排序。如果添加另一行并且还需要考虑手动排序，事情就会变得更加复杂。

ID    Manual     System
-----------------------
1      null      300
2      2         380
3      null      500
4      null      200
5      5         100

所以新的排序将是 4,2,1,3,5

ID    Manual     System
-----------------------
4      null      200
2      2         380
1      null      300
3      null      200
5      5         100

有什么想法吗？我尝试过 Rank、Dense_Rank、Row_Number 等。

对于我的示例来说，给出的解决方案似乎是正确的。我忘了提到第三列 personID 也是这里的一个因素。

ID    Manual     System    PersonID
-------------------------------------
4      null      200         22
2      2         380         22
1      null      300         22
3      null      200         22
5      5         100         22
8      1         210         25
6      1         480         25
7      null      600         25
9      4         800         25
10     null      990         25

所以我首先必须按人订购，然后按手动订购，然后按排序。这似乎仍然给我带来了一个问题。

Answer 1

这是我的解决方案:http://sqlfiddle.com/#!3/a32a0/1/0

SELECT *
FROM
(
  SELECT
    ID
    , ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY System)-.1 AS rn
    , Manual
    , System
    , PersonID
  FROM YourTable
) t0
ORDER BY PersonID
  , COALESCE(Manual, RN)

解释如下:

• 我们将行号作为基本行号。但由于我们首先按 PersonID 的高阶索引进行排序，所以我 PARTITION BY...在我之前ORDER BY...这会重置 MANUAL 每个分组的索引
• 在 ROW_NUMBER 的自然排序之间存在平局的情况和 MANUAL排序时，我减去 .1((0,1) 之间的任意数量)。这优先考虑 MANUAL平局时的值(value)
• 在排序最终结果时，我ORDER BY PARTITION BY首先值，首先确保正确分组，然后按 MANUAL 的第一个非空值排序和RN

尝试一下。 +指向前两个答案的起点。我使用其中一个作为起点并从那里重写。

编辑:删除了 .1 的减法并添加了一个新的排名函数，该函数“欺骗”优化器更喜欢手动而不是排名。我不知道这是否适用于所有情况，或者优化器在其他情况下是否无法按此顺序给出结果，但我想包括这些发现，以防万一它们有帮助。

我更新的查询如下:

SELECT *
  FROM
  (
    SELECT
      ID
      , ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY System) AS rn
      , ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Manual) AS rn_throwaway
      , Manual
      , System
      , PersonID
    FROM YourTable
  ) t0
ORDER BY PersonID
  , COALESCE(Manual, RN)

使用中的示例位于 http://sqlfiddle.com/#!3/1831d/55/0和 http://sqlfiddle.com/#!3/a32a0/9/0