SQL中弱关联表的映射字段

Question

我正在搜索一个SQL-Query，该查询可以将一组单个大小的项目映射到一组单个大小的存储桶中。

我想满足以下条件：

• 桶的大小必须大于或等于物品的大小。
• 每个存储桶只能包含一个项目，或者将其保留为空。
• 每一件物品只能放在一个桶中。
• 没有项目可以拆分为多个存储桶。
• 我想以某种方式填充存储桶，即首先填充最小的未使用存储桶。
• 然后可以按大小或ID排序初始商品和存储桶集，但不能递增
• 初始存储桶和商品集的大小和ID可以是任意的，并且不能以已知的最小值开始
• 当存在有效的映射时，结果必须总是正确的
• 如果没有有效的映射（例如，如果项目多于存储桶），则允许结果不正确，但是，如果结果为空集或具有指示错误结果的另一个属性/信号，我将不胜感激。

举个例子，假设我的bucket和items表看起来像这样：

Bucket:                     Item:
+---------------------+     +---------------------+
| BucketID | Size     |     | ItemID   | Size     |
+---------------------+     +---------------------+
| 1        | 2        |     | 1        | 2        |
| 2        | 2        |     | 2        | 2        |
| 3        | 2        |     | 3        | 5        |
| 4        | 4        |     | 4        | 11       |
| 5        | 4        |     | 5        | 12       |
| 6        | 7        |     +---------------------+
| 7        | 9        |
| 8        | 11       |
| 9        | 11       |
| 10       | 12       |
+---------------------+

然后，我想要一个映射，该映射返回以下结果表：

Result:
+---------------------+
| BucketID | ItemID   |
+---------------------+
| 1        | 1        |
| 2        | 2        |
| 3        | NULL     |
| 4        | NULL     |
| 5        | NULL     |
| 6        | 3        |
| 7        | NULL     |
| 8        | 4        |
| 9        | NULL     |
| 10       | 5        |
+---------------------+

由于没有外键关系或某些东西，我可以将列固定到其对应的存储桶（但只有关系Bucket.Size> =
Item.Size），我很难用有效的SQL查询描述结果。每当我使用联接或子选择时，我都会在存储桶中获得较大的项目（例如，在尺寸为12的存储桶中有一个尺寸为2的项目，而尺寸为2的存储桶仍然可用），或者在相同的项目中多个桶。

我现在花了一些时间自己找到解决方案，我要说的是，最好不要在SQL中声明问题，而在应用程序中声明表，这是更好的选择。

您认为在SQL中可以完成此任务吗？如果是这样，如果您能帮助我解决工作中的问题，我将不胜感激。

编辑：该查询至少应与Oracle，Postgres和SQLite数据库兼容

编辑II：在示例查询上方具有给定测试集的SQL提琴，返回错误结果，但接近，结果看起来像http://sqlfiddle.com/#!15/a6c30/1

Answer 1

使用@SoulTrain中的表定义（但要求数据先进行排序）：

; WITH ORDERED_PAIRINGS AS (
    SELECT i.ITEMID, b.BUCKETID, ROW_NUMBER() OVER (ORDER BY i.SIZE, i.ITEMID, b.SIZE, b.BUCKETID) AS ORDERING, DENSE_RANK() OVER (ORDER BY b.SIZE, b.BUCKETID) AS BUCKET_ORDER, DENSE_RANK() OVER (PARTITION BY b.BUCKETID ORDER BY i.SIZE, i.ITEMID) AS ITEM_ORDER
    FROM @ITEM i
    JOIN @BUCKET b
      ON i.SIZE <= b.SIZE
), ITEM_PLACED AS (
    SELECT ITEMID, BUCKETID, ORDERING, BUCKET_ORDER, ITEM_ORDER, CAST(1 as int) AS SELECTION
    FROM ORDERED_PAIRINGS
    WHERE ORDERING = 1
    UNION ALL
    SELECT *
    FROM (
        SELECT op.ITEMID, op.BUCKETID, op.ORDERING, op.BUCKET_ORDER, op.ITEM_ORDER, CAST(ROW_NUMBER() OVER(ORDER BY op.BUCKET_ORDER) as int) as SELECTION
        FROM ORDERED_PAIRINGS op
        JOIN ITEM_PLACED ip
          ON op.ITEM_ORDER = ip.ITEM_ORDER + 1
         AND op.BUCKET_ORDER > ip.BUCKET_ORDER
    ) AS sq
    WHERE SELECTION = 1
)
SELECT *
FROM ITEM_PLACED