SQL为用户分配id的场景

Question

我有一个如下的sql场景，我一直在努力改进。

有一个“退货”表，其中包含针对商店的商品退货的 ID。其结构如下。

Returns
-------------------------
Return ID | Shop | Item
-------------------------
  1         Shop1  Item1
  2         Shop1  Item1
  3         Shop1  Item1
  4         Shop1  Item1
  5         Shop1  Item1

还有一张供应商表，其中包含商店、供应商和项目，如下所示。

Supplier
---------------------------------
Supplier | Shop | Item  | Volume
---------------------------------
  supp1    Shop1   Item1    20%
  supp2    Shop1   Item1    80%

现在，如您所见，supp1 向 shop1 供应 item1 总量的 20%，supp2 向 shop1 供应 Item1 的 80%。同一 Shop1 的同一 Item1 有 5 次退货。 现在我需要将任意四个返回 ID 分配给 Supp1，并将剩余一个返回 ID 分配给 supp2。这种数量的分配是基于供应商的供应量百分比的比率。这种分配根据供应物品的数量比例而变化。

现在我尝试了一种使用临时表来使用 RANK 的方法，如下所示。

临时表 1 将包含商店、退货 ID、商品、退货 ID 总数和退货 ID 排名。

临时表2将有商店、供应商、项目及其比例和比例等级。

现在，我面临着将顶级退货 ID 分配给顶级供应商的困难，如上所示。由于 SQL 没有循环，我怎样才能实现这一点。我已经结合了几种方法来做到这一点。

我的环境是Teradata（ANSI SQL就足够了）。

Answer 1

更新：
您需要循环，这里有一些 SQL 代码，您可以将其用作起点。
基本上我使用临时 tabes 和 ROW_NUMBER()。
对于我的示例，我使用 SQL SERVER 2008。

请尝试以下操作：

-- gather suppliers in temp table
DECLARE @SupplierTemp table  
        (  [RowId] int
          ,[Supplier] nvarchar (50)
          ,[ReturnCount] int ) 

-- gather supplier with return count
INSERT INTO @SupplierTemp
  SELECT ROW_NUMBER() OVER(ORDER BY [Supplier].[Supplier] DESC, [Supplier].[Supplier]) 
        ,[Supplier].[Supplier]
        , COUNT([Supplier].[Supplier])*[Supplier].[Volume]/100 AS ReturnCount 
  FROM [Supplier] 
  INNER JOIN [Returns] ON (([Returns].[Item] = [Supplier].[Item])  
                         AND ([Returns].[Shop] = [Supplier].[Shop])) 
  GROUP BY [Supplier].[Supplier], [Supplier].[Volume]
  ORDER BY [Supplier].[Supplier] 

-- gather returns in temp table
DECLARE @ReturnsTemp table  
       (  [RowId] int
         ,[Id] int) 

-- gather returns
INSERT INTO @ReturnsTemp
  SELECT ROW_NUMBER() OVER(ORDER BY [Returns].[Id] DESC, [Returns].[Id]) 
        ,[Returns].[Id]
  FROM [Returns] 

-- gather results in temp table
DECLARE @ResultsTemp table  
       (  [Supplier] nvarchar(50)
         ,[Id] int) 

DECLARE @rrowid as int
DECLARE @rid as int

-- loop over all suppliers
-- loop once for each [ReturnCount]
-- find the next avialable Id
DECLARE @srowid as int
DECLARE @loopCnt as int
DECLARE @supplier as nvarchar(50)

-- get first supplier   
SELECT @srowid = (SELECT MIN([RowId]) FROM @SupplierTemp)
SELECT @loopCnt = [ReturnCount] FROM @SupplierTemp WHERE [RowId] = @srowid
SELECT @supplier = [Supplier] FROM @SupplierTemp WHERE [RowId] = @srowid

-- loop over suppliers
WHILE @srowid IS NOT NULL
  BEGIN
    -- loop of number of returns    
    WHILE @loopCnt > 0
      BEGIN
        -- find the Id to return
        SELECT @rrowid = (SELECT MIN([RowId]) FROM @ReturnsTemp)
        SELECT @rid = [Id] FROM @ReturnsTemp WHERE [RowId] = @rrowid

        INSERT INTO @ResultsTemp VALUES (@supplier, @rid)

        DELETE FROM @ReturnsTemp WHERE [RowId] = @rrowid

        SELECT @loopCnt = @loopCnt - 1
      END

    -- delete current item from table to keep loop moving forward...      
    DELETE FROM @SupplierTemp WHERE [RowId] = @srowid

    -- get next supplier.
    SELECT @srowid = (SELECT MIN([RowId]) FROM @SupplierTemp)
    SELECT @loopCnt = [ReturnCount] FROM @SupplierTemp WHERE [RowId] = @srowid
    SELECT @supplier = [Supplier] FROM @SupplierTemp WHERE [RowId] = @srowid
  END

SELECT * FROM @ResultsTemp