根据SQL中的下一个和上一个记录进行复杂的排序

护林员

这是关于基础的SQL下一个和上一个记录排序后续问题
3 K5 H! P" x! {- Y# ~$ w但现在变得更加复杂，例如：
7 W( l- G* d1 T9 x) h[ol]如果一个字母与两个字母匹配，我想改变顺序，以匹配以下记录。
如找不到匹配项，应按字母顺序正常排序。
这些ID可能不成功，记录顺序不正确。[SQLFiddle演示][/ol]创建脚本和SQL Fiddle演示]
create table Parent (id [bigint] IDENTITY(1,2),number bigint NOT NULL,PRIMARY KEY (id))GOcreate 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))GODECLARE @ParentIdentity BIGINTINSERT Parent (number) VALUES (2)SET @ParentIdentity = @@IDENTITYINSERT Child (parentId,letter) VALUES (@ParentIdentity,'A')GO'A')GO当前查询
( U" S5 z2 m9 V: G! A目前，我正在对此查询进行排序：
+ g9 Q3 Y- b* h3 \6 x5 ~2 O;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_elementFROM Child),CTE2 AS (SELECT c1.id,c1.parentid,c1.letter,c2.parentid as c2parentidFROM CTE c1INNER JOIN CTE c2ON c1.last_element = 1AND c2.first_element = 1AND c1.seq_id   1 = c2.seq_id),CTE3 AS (SELECT C.parentid,C.idFROM CTE2INNER JOIN child C ON CTE2.c2parentid = C.parentidAND C.letter = CTE2.letter)SELECT P.number,C.letterFROM Child CJOIN Parent P ON C.parentId = P.idLEFT JOIN CTE3 ON CTE3.id = C.idORDER BY P.number,ISNULL(CTE3.id,0) DESC,C.letter当前结果集
1 p! j: p6 z7 Z. o! lnumber               letter-------------------- ------                                                                               A                                                                               C2                                                                                                                                                                                                                                                                                                                                B2                                                                                                                                                                                                                                                                                                                                C3 3                                                                                                                                                                                                     B3 3                                                                                                                                                                                                     D预期结果集
为了澄清我的实际意图，以下是预期结果集：
1 p. o: @3 _1 u0 }% \4 d( d* q8 ?3 Pnumber               letter-------------------- ------                                                                               A                                                                               C2                                                                                                                                                                                                                                                                                                                                C 2                                                                                                                                                                                                                                                                                                                                B   3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          B3 3                                                                                                                                                                                                     D其它要求和问题
3 C2 V/ U: x; u) J$ t它必须在 SQL Server 2005中工作    。
有一种情况，每个数字使用三个字母，如果只使用最佳匹配，我会很高兴。谁能指出如何应对这种情况？
                                                               
' K( I5 y2 F4 b5 W& U  h: |    解决方案:                                                               
                                                                假如我理解你的要求是正确的，那么你有些部分，parentId并且您希望每个部分都以letters以上部分开始sletter如果是，请尝试以下操作：
;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.letterFROM     t    JOIN    Parent p ON t.parentId = p.idORDER BY    p.number,   CASE WHEN t.letter IN (SELECT ti.letter FROM t ti WHERE ti.parentSeq = t.parentSeq - 1) THEN         WHEN t.letter IN (SELECT ti.letter FROM t ti WHERE ti.parentSeq = t.parentSeq   1) THEN 2         ELSE 1 END,   t.letter