根据 SQL 中的下一条和上一条记录进行排序 [英] Sorting based on next and previous records in SQL

问题描述

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

I am trying to order a specific query by taking the next and previous records into account, but I can't seem to get it done. I would like to order by a number and a letter, but if, for example, the last letter of number 1 is equal to one of the letters of number 2, I want to change the ordering, so that the letter matches with the following record.

创建脚本和 SQL 小提琴演示

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

当前查询

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

Currently I am sorting with this query:

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

To clarify what I actually want to do, here is the expected result set (with C and B of number 2 switched).

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

其他要求和问题

• 它必须在 SQL SERVER 2005 中工作.
• 有一个场景，每个数字使用 3 个字母，如果它只使用最佳匹配，我很高兴.
• 我实际上也对 SQL Server 更高版本的解决方案感兴趣(用于学习)，但这些解决方案并没有回答我的问题.

任何人都可以指出我如何做到这一点的正确方向吗?

Can anyone point me in the right direction on how to do this?

推荐答案

你可以这样做.

1. 使用ROW_NUMBER() 和PARTITION BY
确定每个父级的第一个和最后一个字母
2. 将前一个 id 的最后一条记录与下一个 id 的第一条记录相匹配.
3. 检查第二个父ID是否有与上面选择的字母匹配的字母
4. 使用 LEFT JOIN 并使用 CASE 或 ISNULL 为这样的 id 记录设置更高的优先级其中字母匹配

1. Identify first and last letter of each parent by using ROW_NUMBER() and PARTITION BY
2. Match the last record of the preceding id with the first record of the next id.
3. Check if the second parent id has any letter which matches with the letter selected above
4. Use a LEFT JOIN and use CASE or ISNULL to set a higher priority for such an id record in which the letter had matched

查询

;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

SQL 小提琴

编辑

如果您的 ids 不是连续的，您可以像这样更改 CTE1 和 CTE2 以利用 ROW_NUMBER()OVER(ORDERBY ID) seq_id.

If your ids are not sequential, you can change CTE1 and CTE2 like this to utilize 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
)

其余代码保持不变.

SQL 小提琴

这篇关于根据 SQL 中的下一条和上一条记录进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！