基于 SQL 中下一条和上一条记录的复杂排序 [英] Complex sorting based on next and previous records in SQL

问题描述

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

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

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

1. 如果 1 的任何字母与 2 的任何字母匹配，我想更改排序，以便该字母与以下记录匹配.
2. 如果未找到匹配项，则应按字母进行正常排序.
3. 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

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