在数据库中手动设置前10位产品的顺序

Question

我正在为一个购物中心建立一个数据库。客户希望手动设置前10大品牌或商店。他希望随时手动设置订单，例如：

store_Name     priority
Puma             1
Reebok           2
Dominos          3
Nike             4
NumeroUno        5

产品将按优先顺序展示给用户。优先事项1将显示在顶部。

我的问题是，什么时候我想改变商店名称的优先级，就像我希望耐克在搜索的顶端显示一样。

当我将耐克的优先级设置为1时，自动存在的1位置变为2，2位置变为3，等等。如何在Server (存储过程或触发器)中实现此机制？

我使用的是Server 2014，我的表有数千个存储区。我想要10个有选择的商店，这是显示在移动应用程序顶部。

Answer 1

只是为了好玩，您可以设计一个数据模型，将一个行更新为秩N，需要扫描N行，只更新1行的值。显示顶级N行还需要扫描N行。一个缺点是过滤到Nth级别也要求你扫描N行。如果某人的表中有许多行，并且不希望更新数百万行以执行单个级别更改，这可能会很有用。

关键是为级别存储一个单独的内部值，该值与显示的秩不同，但仍保持正确的顺序。在这里，我将将内部优先级存储为NUMERIC(10,6)。对于每个更新，我们都可以找到以前的RAW_PRIORITY值，并从中减去0.000001。这将只保留一个行更新的顺序。有了这一精确性，最终用户在问题开始发生之前，最多会将项目的排名更改一百万次。根据您的需求，可以进一步提高精度和规模。

定义表：

DROP TABLE IF EXISTS dbo.X_STORES;

CREATE TABLE dbo.X_STORES (STORE_NAME VARCHAR(100), RAW_PRIORITY NUMERIC(10,6));

INSERT INTO dbo.X_STORES VALUES
('Puma',1),
('Reebok',2),
('Dominos',3),
('Nike',4),
('NumeroUno',5)

CREATE INDEX X_STORES__PRIORITY ON dbo.X_STORES (RAW_PRIORITY) INCLUDE (STORE_NAME);

根据具体的需求，在RAW_PRIORITY上对表进行集群可能是有意义的。我们还希望有一种观点，以便更容易地以人类可读的格式显示排名，并使更新更容易：

CREATE OR ALTER VIEW dbo.X_STORES_PRIORITY_ORDER
AS
SELECT STORE_NAME, ROW_NUMBER() OVER (ORDER BY RAW_PRIORITY) [PRIORITY], RAW_PRIORITY
FROM dbo.X_STORES;

随着我们的开始日期的确定，耐克是第四名：

SELECT STORE_NAME, [PRIORITY]
FROM dbo.X_STORES_PRIORITY_ORDER
ORDER BY [PRIORITY];

结果：

╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Puma       ║        1 ║
║ Reebok     ║        2 ║
║ Dominos    ║        3 ║
║ Nike       ║        4 ║
║ NumeroUno  ║        5 ║
╚════════════╩══════════╝

让我们把耐克改成第二名：

UPDATE dbo.X_STORES
SET RAW_PRIORITY = (
    SELECT v.RAW_PRIORITY - 0.000001
    FROM dbo.X_STORES_PRIORITY_ORDER v
    ORDER BY v.RAW_PRIORITY OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
)
WHERE STORE_NAME = 'Nike';

结果：

╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Puma       ║        1 ║
║ Nike       ║        2 ║
║ Reebok     ║        3 ║
║ Dominos    ║        4 ║
║ NumeroUno  ║        5 ║
╚════════════╩══════════╝

现在，让我们将Dominos改为第一位：

UPDATE dbo.X_STORES
SET RAW_PRIORITY = (
    SELECT v.RAW_PRIORITY - 0.000001
    FROM dbo.X_STORES_PRIORITY_ORDER v
    ORDER BY v.RAW_PRIORITY OFFSET 0 ROW FETCH NEXT 1 ROW ONLY
)
WHERE STORE_NAME = 'Dominos';

结果：

╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Dominos    ║        1 ║
║ Puma       ║        2 ║
║ Nike       ║        3 ║
║ Reebok     ║        4 ║
║ NumeroUno  ║        5 ║
╚════════════╩══════════╝

Answer 2

我已经创建了一个小脚本，我认为它能满足你的要求。

我使用划_数来维护表上的顺序。还有其他的排名_函数。

这可能是一个昂贵的操作，取决于您的环境。

具有下列值的表：

--store_Name     priority
--Puma             1
--Reebok           2
--Dominos          3
--Nike             4
--NumeroUno        5

SET NOCOUNT ON

-- I create a table for this example
-- store_name a varchar is not the best primary key 
-- priority is not a good column name 

DECLARE @STORE TABLE(
  store_name varchar(30) not null PRIMARY KEY CLUSTERED,
  [priority] smallint not null
  )


  -- adding the values according to the example
  INSERT INTO @STORE (store_name,[priority])
  SELECT 'Puma',1
  UNION ALL
  SELECT 'Reebok', 2
  UNION ALL
  SELECT 'Dominos', 3
  UNION ALL
  SELECT 'Nike', 4
  UNION ALL
  SELECT 'NumeroUno', 5

  -- showing what we have in the table
    SELECT store_name, [priority]
      FROM @STORE
    ORDER BY [priority]

-- setting priority one to nike
  UPDATE @STORE
  SET [priority] = 1
  WHERE store_name = 'Reebok'


-- maintaining the priority order on the table according to the example
    UPDATE @STORE
    SET S.[priority] =  R.THE_PRIORITY
    FROM @STORE S
    INNER JOIN (

                SELECT  THE_PRIORITY =ROW_NUMBER() OVER (ORDER BY [PRIORITY],CASE WHEN store_name = 'Reebok' THEN 0 ELSE 1 END)
                     ,store_name
                     ,[priority]
              FROM @STORE


    ) AS R ON S.store_name = R.store_name
    WHERE R.THE_PRIORITY IS NOT NULL

--showing the re-ordered table
    SELECT store_name, [priority]
      FROM @STORE
    ORDER BY [priority]