sql - 递归sql分配直到数量用完

Question

我正在尝试执行许可证 ID 号的递归连接分配，直到数量“用完”为止，使用 CTE 样式，union all 递归语句，但我不能完全让这些 peices 一起工作。

我一直遇到的问题是许多许可证可以涵盖 1 个实例，所以当我加入许可证表时，我会得到很多行。欢迎提出任何建议。

TABLE: INSTALLEDSW      
INSTALLEDSW_ID  PRODUCT_ID  PRODUCT_NAME
200             1000        MY SOFTWARE
201             1000        MY SOFTWARE
202             1000        MY SOFTWARE
203             1000        MY SOFTWARE
204             1000        MY SOFTWARE
205             1000        MY SOFTWARE
206             1000        MY SOFTWARE
207             1000        MY SOFTWARE
208             1000        MY SOFTWARE


TABLE:  LICENSE          
LICENSE_ID  PRODUCT_ID  LICENSE_NAME    QUANTITY
100         1000        MY LICENSE      1 
101         1000        MY LICENSE      5 
102         1000        MY LICENSE      1


Desired Result Set  
INSTALLEDSW_ID      LICENSE_ID
200                 100
201                 101
202                 101
203                 101
204                 101
205                 101
206                 102
207                 (null)
208                 (null)

Answer 1

为此您不需要递归 CTE。相反，您需要许可证的累计总和才能获得数量范围。最终结果只是之后的连接。

假设您使用的是支持此功能的 Postgres、SQL Server 2012 或 Oracle。在其他数据库中，您可以使用相关子查询而不是窗口函数来进行必要的计算。

select l.*, SumQTy - Qty + 1 as StartQTY, QTY as EndQTY
from (select l.*,
             sum(quantity) over (partition by product_id order by license_id) as Sumqty
      from license l
     ) l

因此，此许可证是介于 StaartQTY 和 EndQTY 之间的有效许可证。

接下来，您需要将这些应用到您的用户。为此，我们使用 row_number() 来枚举已安装的:

with l as (
     select l.*, SumQTy - Qty + 1 as StartQTY, QTY as EndQTY
     from (select l.*,
                  sum(quantity) over (partition by product_id order by license_id) as Sumqty
           from license l
          ) l
    )
select i.installedsw_id, l.licence_id
from (select i.*,
             row_number() over (partition by product_id order by installedsw_id) as num
      from installedsw i
     ) i left outer join
     l
     on i.installedsw_id = l.installedsw_id and
        i.num between l.startQty and l.endQty