这是一个后续问题根据 SQL 中的下一条记录和上一条记录进行排序 https://stackoverflow.com/questions/30477803/sorting-based-on-next-and-previous-records-in-sql
但现在它变得有点复杂,例如:
- 如果 1 的任何字母与 2 的任何字母匹配,我想更改顺序,以便该字母与下面的记录匹配。
- 如果没有找到匹配项,则应按字母进行正常排序。
- ID 可能不成功,并且记录的顺序不正确。[SQLFiddle 演示] http://sqlfiddle.com/#!3/43070/1
[创建脚本和SQL Fiddle演示] http://sqlfiddle.com/#!3/676b74/1
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
当前查询
目前我正在使用此查询进行排序:
;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
预期结果集
为了澄清我真正想做的事情,这里是预期的结果集:
number letter
-------------------- ------
1 A
1 C
2 C
2 B
3 B
3 D
其他要求和问题
- 它必须工作在SQL Server 2005.
- 有一种情况,每个数字使用 3 个字母,如果它只使用最佳匹配,我很高兴。
谁能指出我如何处理这种情况的正确方向?