根据 SQL 中的下一条和上一条记录进行排序

一尘不染

根据 SQL 中的下一条和上一条记录进行排序

sql

我试图通过考虑下一条和上一条记录来订购特定查询，但我似乎无法完成。我想按一个数字和一个字母排序，但是，例如，如果数字 1 的最后一个字母等于数字 2 的一个字母，我想更改排序，以便该字母与以下内容匹配记录。

create table Parent (
id [bigint] IDENTITY(1,1), 
number bigint NOT NULL,
PRIMARY KEY (id)
)
GO

create table Child (
id [bigint] IDENTITY(1,1), 
parentId BIGINT, 
letter VARCHAR(1) NOT NULL,
PRIMARY KEY (id),
UNIQUE (parentId, Letter),
FOREIGN KEY (parentId) REFERENCES Parent(id)
)
GO

INSERT Parent (number) VALUES (1)
INSERT Parent (number) VALUES (2)
INSERT Parent (number) VALUES (3)

INSERT Child (parentId, letter) VALUES (1, 'A')
INSERT Child (parentId, letter) VALUES (1, 'C')
INSERT Child (parentId, letter) VALUES (2, 'B')
INSERT Child (parentId, letter) VALUES (2, 'C')
INSERT Child (parentId, letter) VALUES (3, 'B')
INSERT Child (parentId, letter) VALUES (3, 'D')

当前查询

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

SELECT P.number, C.letter 
FROM Child C
JOIN Parent P ON C.parentId = P.id
ORDER BY P.number, C.letter

当前结果集

number               letter
-------------------- ------
1                    A
1                    C
2                    B
2                    C
3                    B
3                    D
预期结果集

为了澄清我真正想做的事情，这里是预期的结果集（切换了数字 2 的 C 和 B）。

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

其他要求和问题

它必须在SQL SERVER 2005 中工作。
有一个场景，每个数字使用 3 个字母，如果它只使用最佳匹配，我很高兴。
我实际上也对 SQL Server 更高版本的解决方案感兴趣（用于学习），但那些并没有回答我的问题。
任何人都可以指出我如何做到这一点的正确方向吗？

阅读 175

2021-05-30

共1个答案

一尘不染

你可以做这样的事情。

通过使用ROW_NUMBER()和识别每个父母的第一个和最后一个字母PARTITION BY
将前一个的最后一条记录与下一个id的第一条记录相匹配id。
检查第二个父 ID 是否有与上面选择的字母匹配的字母
使用 aLEFT JOIN和使用CASE或ISNULL为id字母匹配的记录设置更高的优先级

Query

;WITH CTE AS 
(
SELECT id,ParentID,letter,
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.id +1 = c2.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   C
2   B
3   B
3   D

编辑

如果您ids不是顺序的，您可以更改CTE1并CTE2像这样使用ROW_NUMBER()OVER(ORDER BY ID) seq_id.

;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
)

2021-05-30