sql - 如何按列和下一行排序并按另一列排序？

Question

我有一个包含四列的表格:ID、isError、SolidLine 和 HighestError。

每一行通过 SolidLine 列与另一行相关。所以我们在表中有两个相关的行。

例如，ID 为 1 和 2 的行之间的关系为 SolidLine(5)。

----------------------------------------------------------------------
|      ID      |    isError     |      SolidLine   |    HighestError
----------------------------------------------------------------------
|       1      |     0          |        5         |     1
|       2      |     0          |        5         |     1
|       3      |     0          |        8         |     1
|       4      |     0          |        8         |     1
|       5      |     1          |        10        |     50 
|       6      |     0          |        10        |     1
|       7      |     1          |        4         |     80
|       8      |     0          |        4         |     1
|       9      |     1          |        7         |     80
|      10      |     0          |        7         |     1
|      11      |     0          |        3         |     1 
|      12      |     0          |        3         |     1
----------------------------------------------------------------------

我想按以下条件对表格进行排序:

If isError is 1, take the next row by SolidLine, then order by HighestError

所以希望的结果应该是这样的:

----------------------------------------------------------------------
|      ID      |    isError     |      SolidLine   |    HighestError
----------------------------------------------------------------------
|       7      |     1          |        4         |   80
|       8      |     0          |        4         |   1
|       9      |     1          |        7         |   80
|       10     |     0          |        7         |   1
|       5      |     1          |        10        |   50
|       6      |     0          |        10        |   1
|       1      |     0          |        5         |   1
|       2      |     0          |        5         |   1
|       3      |     0          |        8         |   1
|       4      |     0          |        8         |   1
|       11     |     0          |        3         |   1
|       12     |     0          |        3         |   1 
----------------------------------------------------------------------

第一行成为第一行，因为 HighestError 在表 isError 中的最大值等于 1。然后下一行是 ID = 8 因为它 SolidLine 与 ID = 7 的行具有相同的 SolidLine 值。

SolidLine 是始终在一起的对，并且不依赖于 isError 列。

所以由 SolidLine 连接的那对行应该总是在一起。

我尝试了以下查询，但给出了错误的结果:

--it breaks SolidLine ordering. 
SELECT ID, isError, SolidLine, HighestError
FROM SolidThreads
ORDER BY SolidLine, isError, HighestError desc, id

和:

SELECT 
ROW_NUMBER() OVER (PARTITION BY SolidLine ORDER BY isError DESC) [RowNumber],
ID, isError, SolidLine, HighestError
FROM SolidThreads
ORDER BY HighestError desc, id

我做错了什么？或者我该怎么做？

Answer 1

正如您所描述的，您应该能够通过...做到这一点

• 为“This Solid Line Includes an Error Row”添加一列
• 为“这条实线的最大误差”添加一列
• 使用 CASE 表达式根据错误状态更改排序

http://sqlfiddle.com/#!18/84e7a/1

WITH
  SolidThreadsSummary AS
(
  SELECT
    *,
    MAX(isError     ) OVER (PARTITION BY SolidLine)   AS SolidLineHasError,
    MAX(highestError) OVER (PARTITION BY SolidLine)   AS SolidLineMaxError
  FROM
    SolidThreads
)
SELECT
  *
FROM
  SolidThreadsSummary
ORDER BY
  SolidLineHasError DESC,  -- Not really necessary for your data
  SolidLineMaxError DESC,
  CASE WHEN SolidLineHasError > 0 THEN SolidLine ELSE 1 END,
  isError DESC,
  id

如果对不总是按 id 连续(对于不包含错误的对)，这可能会更稳健一些......

http://sqlfiddle.com/#!18/84e7a/2

WITH
  SolidThreadsSummary AS
(
  SELECT
    *,
    MAX(isError     ) OVER (PARTITION BY SolidLine)   AS SolidLineHasError,
    MAX(highestError) OVER (PARTITION BY SolidLine)   AS SolidLineMaxError,
    MIN(id          ) OVER (PARTITION BY SolidLine)   AS SolidLineMinID
  FROM
    SolidThreads
)
SELECT
  *
FROM
  SolidThreadsSummary
ORDER BY
  SolidLineHasError DESC,
  SolidLineMaxError DESC,
  CASE WHEN SolidLineHasError > 0 THEN SolidLine ELSE 1 END,
  isError DESC,
  SolidLineMinID,
  id
;