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

我有一个包含四列的表：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----------------------------------------------------------------------我想按以下条件对表格进行排序： 如果isError为1，则按SolidLine进行下一行，然后按 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具有与行相同的值SolidLine且 = 7。ID总是成对存在，不依赖于SolidLine列。因此，由isError绑定的一对行应始终在一起。我尝试了以下查询，但给出了错误的结果：--it breaks SolidLine ordering.SELECT ID, isError, SolidLine, HighestErrorFROM SolidThreadsORDER BY SolidLine, isError, HighestError desc, id和：SELECTROW_NUMBER() OVER (PARTITION BY SolidLine ORDER BY isError DESC) [RowNumber],ID, isError, SolidLine, HighestErrorFROM SolidThreadsORDER BY HighestError desc, id我究竟做错了什么？或者我该怎么办？ (adsbygoogle = window.adsbygoogle || []).push({}); 最佳答案 正如您所描述的，您应该可以通过...为“此实线包括错误行”添加一列为“此实线的最大误差”添加一列使用CASE表达式更改基于错误状态的排序http://sqlfiddle.com/#!18/84e7a/1WITH SolidThreadsSummary AS( SELECT *, MAX(isError ) OVER (PARTITION BY SolidLine) AS SolidLineHasError, MAX(highestError) OVER (PARTITION BY SolidLine) AS SolidLineMaxError FROM SolidThreads)SELECT *FROM SolidThreadsSummaryORDER 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/2WITH 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 SolidThreadsSummaryORDER BY SolidLineHasError DESC, SolidLineMaxError DESC, CASE WHEN SolidLineHasError > 0 THEN SolidLine ELSE 1 END, isError DESC, SolidLineMinID, id; (adsbygoogle = window.adsbygoogle || []).push({});