sql – 如何重新排序表中的项目

我有一个表(称为my_table),可以简化为：NAME,SEQ_NO,LOCKED.

项目被删除和添加,我想重新排序它们(修改SEQ_NO),使序列总是从1到COUNT(*),锁定的项目保留其SEQ_NO,没有解锁的项目将获得该数字.只有未锁定的项目才会使用新的SEQ_NO进行更新.

例：

这个

NAME  SEQ_NO    LOCKED
Foo   1         N
Bar   3         Y
Abc   4         Y
Baz   5         N
Cde   7         N

将导致：

NAME  SEQ_NO    LOCKED
Foo   1         N
Baz   2         N
Bar   3         Y
Abc   4         Y
Cde   5         N

我该怎么办？

解决方法

有时你的目标是1..COUNT(*)编号,而不是重新编号锁定行会导致无法解决的冲突.例如：

NAME  SEQ_NO    LOCKED
Foo    1        N
Bar   13        Y
Abc   14        Y
Baz    5        N
Cde    7        N

我会假设这个场景所需的输出是：

NAME  SEQ_NO    LOCKED
Foo    1        N
Baz    2        N
Cde    3        N
Bar   13        Y
Abc   14        Y

您的示例显示未锁定的数据保持在其原始序列号顺序中,并且锁定的数据显然不会得到新的数字.

我假设原始数据中没有重复的序列号.

快速摘要

这是一个有趣和棘手的问题.重新排序数据的关键是知道在哪里放置未锁定的行.在示例数据中：

NAME  OLD_SEQ   LOCKED   NEW_SEQ
Foo   1         N        1
Bar   3         Y        3
Abc   4         Y        4
Baz   5         N        2
Cde   7         N        5

我们可以给解锁的行一个从1..3计数的序列号,所以我们最终得到一对ord：旧序列A {1：1,2：5,3：7}.我们可以为结果集1生成一个插槽列表.我们从这些插槽的列表中删除那些被锁定行保留的插槽,留下{1,2,5}作为被重新排序的列表中被解锁行占用的插槽列表.然后我们也按顺序编号,留下对ord：new B {1：1,2：2,3,5].然后,我们可以在第一个字段中加入这两个列表A和B,并排除排序,以便留下新的新旧数字：C {1：1,5：7}.锁定的行会产生一组新的旧值：在每种情况下new = old,因此D {3：3,4：4}.最终结果是C和D的并集,所以结果集包含：

>新序列号1中的旧序列号1;
>新5中的5;
>(新3中的旧3);
>(新4中的旧4);和
>旧7在新5.

这适用于锁定行的序列号为13和14的情况;解锁的行被分配新的序列号1,锁定的行保持不变.对这个问题的评论中有一个提到“1锁定,5解锁,10锁定”;这将产生’1锁定,2解锁,10锁定’.

在sql中执行此操作需要相当多的sql.有一个良好的OLAP功能的命令可能能够比我的代码更快地到达那里.并且将SELECT结果转换为UPDATE语句也很棘手(并没有完全由我解决).但是能够以正确的结果顺序获得数据是至关重要的,解决的关键是由列表A和B表示的排序步骤.

TTQD – 测试驱动查询设计

与任何复杂的SQL查询操作一样,秘密是逐步构建查询.如上所述,我们需要对待锁定和解锁的行进行不同的处理.在这种情况下,目标最终是一个UPDATE语句,但是我们需要知道如何为UPDATE生成数据,所以我们首先执行SELECT.

可重行行

-- Query 1
SELECT Name,Seq_No
  FROM My_Table
 WHERE Locked = 'N'
 ORDER BY Seq_No;

NAME  SEQ_NO
Foo   1
Baz   5
Cde   7

适当时,可以使用ORDER BY子句排序,但子查询通常不允许ORDER BY子句,我们需要生成一个数字.使用OLAP功能,您可以更紧凑地执行此操作.在Oracle中,您可以使用ROWNUM来生成行号.有一个技巧可以在任何DBMS中工作,尽管不是特别快.

假定没有锁定行的干扰的重排行

-- Query 2
SELECT m1.Name,m1.Seq_No AS Old_Seq,COUNT(*) AS New_Seq
  FROM My_Table m1
  JOIN My_Table m2
    ON m1.Seq_No >= m2.Seq_No
 WHERE m1.Locked = 'N' AND m2.Locked = 'N'
 GROUP BY m1.Name,m1.Seq_No
 ORDER BY New_Seq;

NAME  Old_Seq   New_Seq
Foo   1         1
Baz   5         2
Cde   7         3

这是一个非等式,这是什么使这不是一个特别快的操作.

不可逾越的行

-- Query 3
SELECT Name,Seq_No
  FROM My_Table
 WHERE Locked = 'Y'
 ORDER BY Seq_No;

NAME  Seq_No
Bar   3
Abc   4

新序列号

假设我们设法得到一个数字列表,1..N(样本数据中N = 5).我们从该列表中删除离开(1,5)的锁定条目(3,4).当这些排名(1 = 1,= 3,3 = 5)时,我们可以使用解锁记录新序列加入排名,但使用另一个数字作为记录的最后序列号.这只是让我们解决几个小问题.首先,产生每个数字1..N;我们可以做一个非常小的非等同技巧,但应该有一个更好的方法：

-- Query 4
SELECT COUNT(*) AS Ordinal
  FROM My_Table AS t1
  JOIN My_Table AS t2
    ON t1.Seq_No >= t2.Seq_No
 GROUP BY t1.Seq_No
 ORDER BY Ordinal;

Ordinal
1
2
3
4
5

然后,我们可以从该列表中删除锁定的序列号：

-- Query 5
SELECT Ordinal
  FROM (SELECT COUNT(*) AS ordinal
          FROM My_Table t1
          JOIN My_Table t2
            ON t1.Seq_No <= t2.Seq_No
         GROUP BY t1.Seq_No
       ) O
 WHERE O.Ordinal NOT IN (SELECT Seq_No FROM My_Table WHERE Locked = 'Y')
 ORDER BY Ordinal;

 Ordinal
 1
 2
 5

现在我们需要对这些进行排名,这意味着另一个自我加入,但是这一次就是这个表达.时间使用’通用表表达式’或CTE,也称为’WITH子句’：

-- Query 6
WITH HoleyList AS
    (SELECT ordinal
       FROM (SELECT COUNT(*) ordinal
               FROM My_Table t1
               JOIN My_Table t2
                 ON t1.seq_no <= t2.seq_no
              GROUP BY t1.seq_no
            ) O
      WHERE O.Ordinal NOT IN (SELECT Seq_No FROM My_Table WHERE Locked = 'Y')
    )
SELECT H1.Ordinal,COUNT(*) AS New_Seq
  FROM HoleyList H1
  JOIN HoleyList H2
    ON H1.Ordinal >= H2.Ordinal
 GROUP BY H1.Ordinal
 ORDER BY New_Seq;

Ordinal  New_Seq
1        1
2        2
5        3

整理

因此,现在我们需要使用Query 2加入该结果,以获取解锁行的最终数字,然后与Query 3的并集获取所需的输出.当然,我们必须在输出中获得锁定的正确值.还在逐步：

-- Query 7
WITH
Query2 AS
   (SELECT m1.Name,COUNT(*) AS New_Seq
      FROM My_Table m1
      JOIN My_Table m2 ON m1.Seq_No <= m2.Seq_No
     WHERE m1.Locked = 'N' AND m2.Locked = 'N'
     GROUP BY m1.Name,m1.Seq_No
   )
HoleyList AS
   (SELECT ordinal
      FROM (SELECT COUNT(*) AS ordinal
              FROM My_Table t1
              JOIN My_Table t2
                ON t1.seq_no <= t2.seq_no
             GROUP BY t1.seq_no
           ) O
      WHERE O.Ordinal NOT IN (SELECT Seq_No FROM My_Table WHERE Locked = 'Y')
    )
Reranking AS    
   (SELECT H1.Ordinal,COUNT(*) AS New_Seq
      FROM HoleyList H1
      JOIN HoleyList H2
        ON H1.Ordinal >= H2.Ordinal
     GROUP BY H1.Ordinal
   )
SELECT r.Ordinal,r.New_Seq,q.Name,q.Old_Seq,'N' Locked
  FROM Reranking r
  JOIN Query2    q
    ON r.New_Seq = q.New_Seq
 ORDER BY r.New_Seq;

Ordinal  New_Seq  Name  Old_Seq  Locked
1        1        Cde   7        N
2        2        Baz   5        N
5        3        Foo   1        N

这需要与查询3的变体相结合：

-- Query 3a
SELECT Seq_No Ordinal,Seq_No New_Seq,Name,Seq_No Old_Seq,Locked
  FROM My_Table
 WHERE Locked = 'Y'
 ORDER BY New_Seq;

Ordinal  New_Seq  Name  Old_Seq  Locked
3        3        Bar   3        Y
4        4        Abc   4        Y

结果集

结合这些产量：

-- Query 8
WITH
Query2 AS
   (SELECT m1.Name,COUNT(*) AS New_Seq
      FROM HoleyList H1
      JOIN HoleyList H2
        ON H1.Ordinal >= H2.Ordinal
     GROUP BY H1.Ordinal
   )
Query7 AS
   (SELECT r.Ordinal,'N' Locked
      FROM Reranking r
      JOIN Query2    q
        ON r.New_Seq = q.New_Seq
   )
Query3a AS
   (SELECT Seq_No Ordinal,Locked
      FROM My_Table
     WHERE Locked = 'Y'
   )
SELECT Ordinal,New_Seq,Old_Seq,Locked
  FROM Query7
UNION
SELECT Ordinal,Locked
  FROM Query3a
 ORDER BY New_Seq;

结果如下：

Ordinal  New_Seq  Name  Old_Seq  Locked
1        1        Cde   7        N
2        2        Baz   5        N
3        3        Bar   3        Y
4        4        Abc   4        Y
5        3        Foo   1        N

所以,尽可能地写一个SELECT语句来正确地命令数据.

转换为UPDATE操作

现在我们必须找到一种方法来将这个怪物变成UPDATE语句.留给我自己的设备,我会考虑一个将查询8的结果选择到临时表中的事务,然后从源表(My_Table)中删除所有记录,并将查询8的结果的相应项目进入原来的表,然后提交.

Oracle似乎不支持动态创建的“每个会话”临时表;只有全局临时表.而且有理由不使用这些,因为他们都是sql标准.然而,它将在这里做的诀窍,我不知道还有什么工作：

与此工作分开：

CREATE GLOBAL TEMPORARY TABLE ReSequenceTable
(
    Name     CHAR(3) NOT NULL,Seq_No   INTEGER NOT NULL,Locked   CHAR(1) NOT NULL
)
ON COMMIT DELETE ROWS;

然后：

-- Query 8a
BEGIN;   -- May be unnecessary and/or unsupported in Oracle
INSERT INTO ReSequenceTable(Name,Seq_No,Locked)
    WITH
    Query2 AS
       (SELECT m1.Name,COUNT(*) AS New_Seq
          FROM My_Table m1
          JOIN My_Table m2 ON m1.Seq_No <= m2.Seq_No
         WHERE m1.Locked = 'N' AND m2.Locked = 'N'
         GROUP BY m1.Name,m1.Seq_No
       )
    HoleyList AS
       (SELECT ordinal
          FROM (SELECT COUNT(*) AS ordinal
                  FROM My_Table t1
                  JOIN My_Table t2
                    ON t1.seq_no <= t2.seq_no
                 GROUP BY t1.seq_no
               ) O
          WHERE O.Ordinal NOT IN (SELECT Seq_No FROM My_Table WHERE Locked = 'Y')
        )
    Reranking AS    
       (SELECT H1.Ordinal,COUNT(*) AS New_Seq
          FROM HoleyList H1
          JOIN HoleyList H2
            ON H1.Ordinal >= H2.Ordinal
         GROUP BY H1.Ordinal
       )
    Query7 AS
       (SELECT r.Ordinal,'N' Locked
          FROM Reranking r
          JOIN Query2    q
            ON r.New_Seq = q.New_Seq
       )
    Query3a AS
       (SELECT Seq_No Ordinal,Locked
          FROM My_Table
         WHERE Locked = 'Y'
       )
    SELECT Name,Ordinal,Locked
      FROM Query7
    UNION
    SELECT Name,Locked
      FROM Query3a;

DELETE FROM My_Table;
INSERT INTO My_Table(Name,Locked) FROM ReSequenceTable;
COMMIT;

你可能会用适当的UPDATE来做到这一点;你需要做一些思考.

概要

这不容易,但可以做到.

关键步骤(至少对我来说)是Query 6的结果集,它解决了更新结果集中未锁定行的新位置.这不是很明显的,但是产生答案至关重要.

其余的只是支持围绕该关键步骤的代码.

如前所述,可能有许多方法来改进一些查询.例如,从表中生成序列1..N可能与SELECT ROWNUM FROM My_Table一样简单,它压缩了查询(非常有益 – 它是详细的).有OLAP功能;一个或多个可能能够帮助排名操作(可能更简洁;像表现更好).

所以,这不是一个抛光的最后答案.但它是正确的大方向的强大推动力.

PoC测试

该代码已针对Informix进行了测试.我不得不使用不同的符号,因为Informix还没有支持CTE.它具有非常方便,非常简单的每个会话由INTO TEMP< temp-table-name>引入的动态临时表.它出现在ORDER BY子句可能出现的地方.因此,我用以下模拟查询8a：

+ BEGIN;
+ SELECT O.Ordinal
  FROM (SELECT COUNT(*) AS ordinal
          FROM My_Table AS t1
          JOIN My_Table AS t2
            ON t1.Seq_No <= t2.Seq_No
         GROUP BY t1.Seq_No
       ) AS O
 WHERE O.Ordinal NOT IN (SELECT Seq_No FROM My_Table WHERE Locked = 'Y')
 INTO TEMP HoleyList;
+ SELECT * FROM HoleyList ORDER BY Ordinal;
1
2
5
+ SELECT H1.Ordinal,COUNT(*) AS New_Seq
  FROM HoleyList AS H1
  JOIN HoleyList AS H2
    ON H1.Ordinal >= H2.Ordinal
 GROUP BY H1.Ordinal
 INTO TEMP ReRanking;
+ SELECT * FROM ReRanking ORDER BY Ordinal;
1|1
2|2
5|3
+ SELECT m1.Name,m1.Seq_No
  INTO TEMP Query2;
+ SELECT * FROM Query2 ORDER BY New_Seq;
Foo|1|1
Baz|5|2
Cde|7|3
+ SELECT r.Ordinal,'N' Locked
  FROM Reranking r
  JOIN Query2    q
    ON r.New_Seq = q.New_Seq
  INTO TEMP Query7;
+ SELECT * FROM Query7 ORDER BY Ordinal;
1|1|Foo|1|N
2|2|Baz|5|N
5|3|Cde|7|N
+ SELECT Seq_NO Ordinal,Locked
  FROM My_Table
 WHERE Locked = 'Y'
  INTO TEMP Query3a;
+ SELECT * FROM Query3a ORDER BY Ordinal;
3|3|Bar|3|Y
4|4|Abc|4|Y
+ SELECT Ordinal,Locked
  FROM Query3a
  INTO TEMP Query8;
+ SELECT * FROM Query8 ORDER BY Ordinal;
1|1|Foo|1|N
2|2|Baz|5|N
3|3|Bar|3|Y
4|4|Abc|4|Y
5|3|Cde|7|N
+ ROLLBACK;

sql – 如何重新排序表中的项目

解决方法

猜你在找的MsSQL相关文章