sql - SQL弱关联表字段映射

Question

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

我想满足以下条件:

• 桶的大小必须大于或等于项目的大小。
• 每个桶只能包含一个项目，或者留空。
• 每件元素只能放在一个桶中。
• 任何项目都不能拆分到多个桶中。
• 我想以某种方式填充桶，首先填充最小的未使用桶。
• 然后初始项和桶集可以按大小或 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 Fiddle 在示例查询上方，返回错误结果，但接近于结果可能的样子 http://sqlfiddle.com/#!15/a6c30/1

Answer 1

试试这个...我能够使用 recursive CTE 实现这一点，全部在 1 个 SQL 语句中

我的唯一假设是Bucket 和 Item 数据集已排序。

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