基于 SQL 中下一条和上一条记录的复杂排序

本文介绍了基于 SQL 中下一条和上一条记录的复杂排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是关于排序的后续问题SQL 中的下一条和上一条记录

但现在它变得有点复杂，例如:

But now it gets a little more complex, for example:

如果 1 的任何字母与 2 的任何字母匹配，我想更改排序，以便该字母与以下记录匹配.

如果未找到匹配项，则应按字母进行正常排序.

ID 可能不成功，并且记录的顺序不正确.[SQLFiddle 演示]

[创建脚本和 SQL Fiddle 演示]

create table Parent ( id [bigint] IDENTITY(1,2), number bigint NOT NULL, PRIMARY KEY (id) ) GO create table Child ( id [bigint] IDENTITY(1,2), parentId BIGINT, letter VARCHAR(1) NOT NULL, PRIMARY KEY (id), UNIQUE (parentId, Letter), FOREIGN KEY (parentId) REFERENCES Parent(id) ) GO DECLARE @ParentIdentity BIGINT INSERT Parent (number) VALUES (2) SET @ParentIdentity = @@IDENTITY INSERT Child (parentId, letter) VALUES (@ParentIdentity, 'C') INSERT Child (parentId, letter) VALUES (@ParentIdentity, 'B') INSERT Parent (number) VALUES (3) SET @ParentIdentity = @@IDENTITY INSERT Child (parentId, letter) VALUES (@ParentIdentity, 'D') INSERT Child (parentId, letter) VALUES (@ParentIdentity, 'B') INSERT Parent (number) VALUES (1) SET @ParentIdentity = @@IDENTITY INSERT Child (parentId, letter) VALUES (@ParentIdentity, 'C') INSERT Child (parentId, letter) VALUES (@ParentIdentity, 'A') GO

当前查询目前我正在使用此查询进行排序:

Current query Currently I am sorting with this query:

;WITH CTE AS ( SELECT id,ParentID,letter, ROW_NUMBER() OVER (ORDER BY ID) seq_id, ROW_NUMBER() OVER (PARTITION BY parentId ORDER BY ID) first_element, ROW_NUMBER() OVER (PARTITION BY parentId ORDER BY ID DESC) Last_element FROM Child ), CTE2 AS ( SELECT c1.id, c1.parentid, c1.letter, c2.parentid as c2parentid FROM CTE c1 INNER JOIN CTE c2 ON c1.last_element = 1 AND c2.first_element = 1 AND c1.seq_id + 1 = c2.seq_id ), CTE3 AS ( SELECT C.parentid, C.id FROM CTE2 INNER JOIN child C ON CTE2.c2parentid = C.parentid AND C.letter = CTE2.letter ) SELECT P.number, C.letter FROM Child C JOIN Parent P ON C.parentId = P.id LEFT JOIN CTE3 ON CTE3.id = C.id ORDER BY P.number, ISNULL(CTE3.id,0) DESC, C.letter

当前结果集

number letter -------------------- ------ 1 A 1 C 2 B 2 C 3 B 3 D

预期结果集为了澄清我真正想要做什么，这是预期的结果集:

Expected result set To clarify what I actually want to do, here is the expected result set:

number letter -------------------- ------ 1 A 1 C 2 C 2 B 3 B 3 D

其他要求和问题

• 它必须在 SQL Server 2005 中工作.
• 有一个场景，每个数字使用 3 个字母，如果它只使用最佳匹配，我很高兴.

谁能为我指出如何处理这种情况的正确方向?

Can anyone point me in the right direction on how to deal with this scenario?

推荐答案

如果我理解您的要求，您有 parentId 的某些部分，并且您希望每个部分都以 字母开头s 那些在上一部分 并以 letters 结尾那些在下一部分，如果是的话试试这个:

If I understand your requirement right, You have some parts of parentId and you want each part to start with the letters those are in previous part And end with letters those are in next part, If yes try this:

;WITH t AS ( SELECT c.id, c.parentId, c.letter, dt.parentSeq FROM Child c JOIN ( SELECT ci.parentId, ROW_NUMBER() OVER (ORDER BY p.number) parentSeq FROM Child ci JOIN Parent p ON ci.parentId = p.id GROUP BY ci.parentId, p.number) dt ON c.parentId = dt.parentId ) SELECT p.number, t.letter FROM t JOIN Parent p ON t.parentId = p.id ORDER BY p.number, CASE WHEN t.letter IN (SELECT ti.letter FROM t ti WHERE ti.parentSeq = t.parentSeq - 1) THEN 0 WHEN t.letter IN (SELECT ti.letter FROM t ti WHERE ti.parentSeq = t.parentSeq + 1) THEN 2 ELSE 1 END, t.letter