映射SQL中弱相关表的字段 [英] Mapping fields of weakly related tables in SQL

问题描述

我正在搜索一个SQL查询，可以将一组单个大小的项目映射到一个单独大小的桶。

I'm searching for an SQL-Query that can map a set of items of an individual size to a set off buckets of individual size.

以满足以下条件：

• 存储桶的大小必须大于或等于项目的大小。


• 每个桶只能包含一个项目或留空。


• 每个项目只能放置在一个桶中。


• 
  
• 我希望以某种方式填充值区，最小的未使用值区会先填满。


• 然后初始项目和存储桶集合可以按大小或ID排序，但不是增量


• 初始桶和项目集合的大小和标识可以是任意的，


• 当有有效的映射时，结果必须始终正确


• 如果没有有效的映射，结果可能不正确（例如，如果有比桶更多的项目），但我会感谢，当结果是一个空集或有另一个属性/信号，表示不正确的结果。

• The size of a bucket has to be bigger or equal the size of an item.
• Every bucket can contain only one item or it is left empty.
• Every item can only be placed in one bucket.
• No item can be split to multiple buckets.
• I want to fill the buckets in a way, that the smallest unused buckets are filled first.
• Then initial item and bucket sets can be ordered by size or id, but are not incremental
• Sizes and ids of initial bucket and item sets can be arbitrary and do not start at a known minimum value
• The result has to be always correct, when there is a valid mapping
• The result is allowed to be incorrect if the is no valid mapping (for example if there are more items than buckets), but I would appreciate, when the result is an empty set or has another property/signal that indicates an incorrect result.

举个例子，假设我的bucket和items表如下：

To give you an example, let's say my bucket and items tables look like that:

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       |
+---------------------+

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

Then, I'd like to have a mapping that is returning the following result table:

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查询。每当我使用连接或子选择，我得到桶中的项目，这是大的（如一个大小为2的项目在大小为12的桶，而大小为2的桶仍然可用）或我得到相同的项目多个桶。

Since there is no foreign key relation or something I could fix the columns to their corresponding bucket (but only the relation Bucket.Size >= Item.Size) I'm have a lot of trouble describing the result with a valid SQL query. Whenever I use joins or sub selects, I get items in buckets, that are to big (like having an item of size 2 in a bucket of size 12, while a bucket of size 2 is still available) or I get the same item in multiple buckets.

我花了一些时间来自己找到解决方案，我接近说，最好不要在SQL中声明问题，而是在应用程序，即只是获取表。

I spent some time now to find the solution myself and I am close to say, that it is better not to declare the problem in SQL but in an application, that is just fetching the tables.

你认为这个任务在SQL中是可能的吗？

Do you think this task is possible in SQL? And if so, I would really appreciate if you can help me out with a working query.

编辑：查询应该至少与Oracle，Postgres和SQLite数据库兼容。如果是这样，我将非常感谢。

Edit : The query should be compatible to at least Oracle, Postgres and SQLite databases

编辑II：一个SQL Fiddle与给定的测试集上面的示例查询，返回一个错误的结果，但是接近，结果可能看起来像 http://sqlfiddle.com/#!15/a6c30/1

Edit II: An SQL Fiddle with the given test set above an example query, that returns a wrong result, but is close, to what the result could look like http://sqlfiddle.com/#!15/a6c30/1

推荐答案

使用@SoulTrain的表定义（但需要提前对数据进行排序）：

Using the table definition from @SoulTrain (but requiring the data to be sorted in advance):

; 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

这篇关于映射SQL中弱相关表的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！