主页 / dba / 问题 / 201898
Accepted
Philᵀᴹ
Asked: 2018-03-22 06:23:29 +0800 CST

SQL 中用户定义的排序 [重复]

  • 772

我刚刚阅读了一篇关于用户定义结果集排序的有趣博客文章。

一些应用程序，例如待办事项列表，需要维护用户定义的项目顺序。挑战在于顺序是任意的，并且可以在用户重新排列项目时更改

一个示例表可能是这样的：

create table items
(
  itemid int primary key,
  itemdata varchar(200),
  ...
  ...
  userorder ??? 
);

使用可能的检索查询：

select * from items order by userorder asc;

鉴于我想将更新保持在最低限度，更新原子和检索查询尽可能简单和“高性能”，是否有任何方法可以考虑？

我最初的想法是使用一个额外的TIMESTAMP列，并且只更新单行userorder的值（检索查询使用ing by ），但是当您需要在具有相同值的其他两行之间移动一行时，这会失败.timestampORDERuserorder asc, usertimestamp desc

没有考虑特定的数据库，因为我自己使用的范围很广。

order-by

2 个回答

  1. 鉴于我想将更新保持在最低限度，更新原子和检索查询尽可能简单和“高性能”，是否有任何方法可以考虑？

    我认为这是该博客文章中“真分数”方法的一种深奥变体——优点是不需要引入用户定义的类型（至少如果您使用的是 Postgres）。

    这使用了自然排序属性varbit并将它们的序列映射到二叉树上，以便始终可以varbit在任何两个现有相邻值之间生成另一​​个值：

                              _____ 1 ____
                   ______/            \______
              _ 01 _                          11
            _/      \_                    _/      \_
        001            011            101            111
       /   \          /   \          /   \          /   \
    0001   0011    0101   0111    1001   1011    1101    1111

    create table foo(id serial primary key, userorder varbit unique);

create function adj(orig varbit, b varbit) returns varbit language sql as $$
  select substring(orig for length(orig)-1)
       ||b
       ||substring(orig from length(orig));
$$;

create function f(l varbit, h varbit) returns varbit language plpgsql as $$

begin
  if l is null and h is null then return B'1'; end if;
  if l is null then return adj(h,B'0'); end if;
  if h is null then return adj(l,B'1'); end if;
  if length(l)>length(h) then return adj(l,B'1'); end if;
  return adj(h,B'0');
end;
$$;

    insert into foo(userorder) values(f(null,null));
select * from foo order by userorder;

    编号 | 用户订单
-: | :--------
 1 | 1        

    insert into foo(userorder) values(f(null,B'1'));
select * from foo order by userorder;

    编号 | 用户订单
-: | :--------
 2 | 01       
 1 | 1        

    insert into foo(userorder) values(f(B'01',B'1'));
select * from foo order by userorder;

    编号 | 用户订单
-: | :--------
 2 | 01       
 3 | 011      
 1 | 1        

    insert into foo(userorder)
values(f(B'01',B'011')),(f(B'011',B'1')),(f(B'1',null));

select * from foo order by userorder;

    编号 | 用户订单
-: | :--------
 2 | 01       
 4 | 0101     
 3 | 011      
 5 | 0111     
 1 | 1        
 6 | 11

    dbfiddle在这里

    如果您最初加载大量有序行，您可能希望使用其他算法来生成初始userorder值，因为您将遇到空间使用的最坏情况（每行将比前一行多使用一位userorder）。对于足够多的位，您可以逐步遍历相同长度的值（例如，对于 8 个值：B'0001'、B'0011'、B'0101'、B'0111'、B'1001'、B'1011'、B'1101'、B'1111'）。

    • 5
  2. Best Answer

    介绍

    一些非常古老的 BASIC 语言要求每一行代码都有一个行号。

    编程时，行号通常以 10 的倍数间隔。这使您可以在以后的行之间添加更多代码。

    您的items表应遵循相同的概念。

    基本算法

    1. 以 10 的倍数开始的值以userorder10 开始userid
    2. userorder根据需要更新应用程序
    3. 您重新编号该用户userorder使用 Analytics
    4. 'userorder' 的值保持为 10 的倍数。
    5. commit数据变化。

    重枚举代码

    这基本上是我用过的：

    merge into items a
using (
  select i.itemid
    ,10 * row_number() over (partition by i.userid order by i.userorder)
      as new_userorder
  from items i
  where i.userid=?
) b
on (a.itemid = b.itemid)
when matched then update
  set a.userorder=b.new_userorder
;

    我假设此表包含每个人的 TODO 列表，并且每个用户都由userid.

    此示例使用ROW_NUMBER()来自 Oracle。您必须将它替换为您正在使用的任何 RDBMS。

    • 2

相关问题