使用累加和将货物箱分配给卡车

    本章中要讨论的这个问题非常类似于前面的两个问题。这次我将演示累加和是如何简化查询的开发的。

    假设需要将一些大小一致的箱子装载到几辆容量不等的卡车上。实际上这是一个非常常见的资源分配问题，这种问题通常使用游标来解决。

    下面是表的定义以及一些样本数据：  

   CREATE TABLE TRUCK(
    TRUCK_ID INT NOT NULL PRIMARY KEY,
    CAPACITY SMALLINT NOT NULL);

    INSERT INTO TRUCK VALUES(11,3), (22, 2), (33,3);

    CREATE TABLE CARGO_BOX(
    CARGO_BOX_ID INT NOT NULL PRIMARY KEY,
    DESCRIPTION VARCHAR(40));

    INSERT INTO CARGO_BOX VALUES(101,'PEACHES'),(102,'POTATOES'),
(103,'TOMATOES'),(104,'TOMATOES'),(105,'TOMATOES'),
    (106,'PINEAPPLES'),(107,'PINEAPPLES');

    CREATE TABLE BOX_IN_TRUCK(
    TRUCK_ID INT NOT NULL,
    CARGO_BOX_ID INT NOT NULL,
    FOREIGN KEY(TRUCK_ID) REFERENCES TRUCK(TRUCK_ID),
    FOREIGN KEY(CARGO_BOX_ID) REFERENCES CARGO_BOX(CARGO_BOX_ID));

    问题是恰当地填充 BOX_IN_TRUCK 表，意即将箱子分配给卡车，使得没有卡车超载。通常需要使用游标来完成这一任务。不过，如果使用 OLAP 函数，即使没有游标也能完成这一任务。

    让我们验证该查询是否产生了所需的结果：

    INSERT INTO BOX_IN_TRUCK
    SELECT
    TRUCK_CUMULATIVE.TRUCK_ID,
    CARGO_BOX.CARGO_BOX_ID
    FROM
    (SELECT SUM(CAPACITY) OVER(ORDER BY TRUCK_ID) - CAPACITY + 1 AS BOX_FROM,
    SUM(CAPACITY) OVER(ORDER BY TRUCK_ID) AS BOX_TO, CAPACITY,
 TRUCK_ID FROM TRUCK) AS TRUCK_CUMULATIVE
    JOIN
    (SELECT ROW_NUMBER() OVER() AS ROW_NUMBER, CARGO_BOX_ID FROM
 CARGO_BOX) AS CARGO_BOX
    ON ROW_NUMBER BETWEEN BOX_FROM AND BOX_TO;

    为了理解其工作原理，让我们检索查询中涉及的所有列： 

   SELECT
    TRUCK_CUMULATIVE.TRUCK_ID, BOX_FROM, BOX_TO,
    CARGO_BOX.CARGO_BOX_ID, ROW_NUMBER
    FROM
    (SELECT SUM(CAPACITY) OVER(ORDER BY TRUCK_ID) - CAPACITY + 1 AS BOX_FROM,
    SUM(CAPACITY) OVER(ORDER BY TRUCK_ID) AS BOX_TO, CAPACITY,
 TRUCK_ID FROM TRUCK) AS TRUCK_CUMULATIVE
    JOIN
    (SELECT ROW_NUMBER() OVER() AS ROW_NUMBER, CARGO_BOX_ID FROM
CARGO_BOX) AS CARGO_BOX
    ON ROW_NUMBER BETWEEN BOX_FROM AND BOX_TO;

    TRUCK_IDBOX_FROMBOX_TOCARGO_BOX_ID ROW_NUMBER
    ----------- ----------- ----------- ------------     11131011
    11131022
    11131033
    22451044
    22451055
    33681066
    33681077
    7 record(s) selected.