SQL中弱相关表的字段映射

我正在寻找一个 SQL 查询，它可以将一组单独大小的项目映射到一组单独大小的存储桶。

我想满足以下条件：

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

举个例子，假设我的存储桶和项目表如下所示：

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 Fiddle，它返回错误的结果，但与结果可能的样子很接近http://sqlfiddle.com/#!15/a6c30/1 http://sqlfiddle.com/#!15/a6c30/1

尝试这个... 我能够使用来实现这个recursive CTE，全部合为 1 个单曲SQL陈述

我唯一的假设是桶和物品数据集已排序。

DECLARE @BUCKET TABLE
    (
     BUCKETID INT
     , SIZE INT
    )

    DECLARE @ITEM TABLE
    (
     ITEMID INT
     , SIZE INT
    )
    ;  
    INSERT INTO @BUCKET
    SELECT 1,2 UNION ALL
    SELECT 2,2 UNION ALL
    SELECT 3,2 UNION ALL
    SELECT 4,4 UNION ALL
    SELECT 5,4 UNION ALL
    SELECT 6,7 UNION ALL
    SELECT 7,9 UNION ALL
    SELECT 8, 11 UNION ALL
    SELECT 9, 11 UNION ALL
    SELECT 10,12 

    INSERT INTO @ITEM
    SELECT 1,2 UNION ALL
    SELECT 2,2 UNION ALL
    SELECT 3,5 UNION ALL
    SELECT 4,11 UNION ALL
    SELECT 5,12;

    WITH TOTAL_BUCKETS
    AS (
        SELECT MAX(BUCKETID) CNT
        FROM @BUCKET
        ) -- TO GET THE TOTAL BUCKETS COUNT TO HALT THE RECURSION
        , CTE
    AS (
        --INVOCATION PART
        SELECT BUCKETID
            , (
                SELECT MIN(ITEMID)
                FROM @ITEM I2
                WHERE I2.SIZE <= (
                        SELECT SIZE
                        FROM @BUCKET
                        WHERE BUCKETID = (1)
                        )
                ) ITEMID --PICKS THE FIRST ITEM ID MATCH FOR THE BUCKET SIZE
            , BUCKETID + 1 NEXT_BUCKETID --INCREMENT FOR NEXT BUCKET ID 
            , (
                SELECT ISNULL(MIN(ITEMID), 0)
                FROM @ITEM I2
                WHERE I2.SIZE <= (
                        SELECT SIZE
                        FROM @BUCKET
                        WHERE BUCKETID = (1)
                        )
                ) --PICK FIRST ITEM ID MATCH
            + (
                CASE 
                    WHEN (
                            SELECT ISNULL(MIN(ITEMID), 0)
                            FROM @ITEM I3
                            WHERE I3.SIZE <= (
                                    SELECT SIZE
                                    FROM @BUCKET
                                    WHERE BUCKETID = (1)
                                    )
                            ) IS NOT NULL
                        THEN 1
                    ELSE 0
                    END
                ) NEXT_ITEMID --IF THE ITEM IS PLACED IN THE BUCKET THEN INCREMENTS THE FIRST ITEM ID
            , (
                SELECT SIZE
                FROM @BUCKET
                WHERE BUCKETID = (1 + 1)
                ) NEXT_BUCKET_SIZE --STATES THE NEXT BUCKET SIZE
        FROM @BUCKET B
        WHERE BUCKETID = 1

        UNION ALL

        --RECURSIVE PART
        SELECT NEXT_BUCKETID BUCKETID
            , (
                SELECT ITEMID
                FROM @ITEM I2
                WHERE I2.SIZE <= NEXT_BUCKET_SIZE
                    AND I2.ITEMID = NEXT_ITEMID
                ) ITEMID -- PICKS THE ITEM ID IF IT IS PLACED IN THE BUCKET
            , NEXT_BUCKETID + 1 NEXT_BUCKETID --INCREMENT FOR NEXT BUCKET ID 
            , NEXT_ITEMID + (
                CASE 
                    WHEN (
                            SELECT I3.ITEMID
                            FROM @ITEM I3
                            WHERE I3.SIZE <= NEXT_BUCKET_SIZE
                                AND I3.ITEMID = NEXT_ITEMID
                            ) IS NOT NULL
                        THEN 1
                    ELSE 0
                    END
                ) NEXT_ITEMID --IF THE ITEM IS PLACED IN THE BUCKET THEN INCREMENTS THE CURRENT ITEM ID
            , (
                SELECT SIZE
                FROM @BUCKET
                WHERE BUCKETID = (NEXT_BUCKETID + 1)
                ) NEXT_BUCKET_SIZE --STATES THE NEXT BUCKET SIZE
        FROM CTE
        WHERE NEXT_BUCKETID <= (
                SELECT CNT
                FROM TOTAL_BUCKETS
                ) --HALTS THE RECURSION
        )
    SELECT 
        BUCKETID
        , ITEMID
    FROM CTE