使用 ROW_NUMBER() 为客人分配房间

    让我们考虑一个简单的问题。假设有两个表：一个是宾馆可用房间的列表，一个是刚到的宾馆客人的列表。我们需要将可用的房间分配给刚到的客人，那么如果有空房间的话，应该尽量使每个客人得到一个房间。为了解决这样的问题，通常需要打开两个游标，一个是用于房间的游标，一个是用于客人的游标，然后迭代这两个游标，直到其中一个游标或者两个游标同时到达结尾处。下面是这个表的定义和一些样本数据： 

   CREATE TABLE ROOM(
    ROOM_ID INT NOT NULL PRIMARY KEY,
    SMOKING CHAR(1) NOT NULL CHECK(SMOKING IN ('N','Y')));
    INSERT INTO ROOM VALUES (121,'Y'),(139,'N'),(142,'N'),(201,'Y'),(202,'N');
    CREATE TABLE GUEST(
    GUEST_ID INT NOT NULL PRIMARY KEY,
    SMOKER CHAR(1) NOT NULL CHECK(SMOKER IN ('N','Y')));
    INSERT INTO GUEST VALUES(321, 'N'),(17,'Y'),(57,'Y'),(91,'Y'),
(2,'N'),(444,'N');
    CREATE TABLE GUEST_ASSIGNMENT(
    GUEST_ID INT NOT NULL PRIMARY KEY,
    ROOM_ID INT NOT NULL UNIQUE,
    FOREIGN KEY(GUEST_ID) REFERENCES GUEST(GUEST_ID),
    FOREIGN KEY(ROOM_ID) REFERENCES ROOM(ROOM_ID));

    如果使用 OLAP 函数 ROW_NUMBER() ，就不需要打开两个游标并迭代房间和客人。相反，我们可以简单地联结这两个表，并在房间与客人之间取得 1 对 1 的对应关系。为了理解其工作原理，让我们首先看看这个选择查询及其输出：

    SELECT ROOM_NUMBER, GUEST_NUMBER, ROOM_ID, GUEST_ID
    FROM
    (SELECT ROW_NUMBER() OVER(ORDER BY ROOM_ID) AS ROOM_NUMBER,
ROOM_ID FROM ROOM) AS R
    JOIN
    (SELECT ROW_NUMBER() OVER(ORDER BY GUEST_ID) AS GUEST_NUMBER,
 GUEST_ID FROM GUEST) AS G
    ON ROOM_NUMBER=GUEST_NUMBER
    ROOM_NUMBERGUEST_NUMBERROOM_IDGUEST_ID
    -------------------- -------------------- 
    111212
    2213917
    3314257
    4420191
    55202321

    这个例子查询将 ROOM 表中的最多一条记录与 GUEST 表中的最多一条记录相联结。注意，您可以不像我那样指定排序（OVER(ORDER BY GUEST_ID)），而是宣称排序不重要（OVER()），甚至请求使用随机排序（OVER(ORDER BY RAND())）。在这种情况下，一个客人得不到一个房间，因为没有足够的空房间。可以随意添加记录到 ROOM 表中，以检验该查询在其他情况下（例如有客人那么多的房间，或者没有客人那么多的房间）的工作情况。

    如果理解了联结的工作原理，填充 GUEST_ASSIGNMENT 表就比较容易了：

    INSERT INTO GUEST_ASSIGNMENT
    SELECT GUEST_ID, ROOM_ID
    FROM
    (SELECT ROW_NUMBER() OVER(ORDER BY ROOM_ID) AS ROOM_NUMBER,
 ROOM_ID FROM ROOM) AS R
    JOIN
    (SELECT ROW_NUMBER() OVER(ORDER BY GUEST_ID) AS GUEST_NUMBER,
 GUEST_ID FROM GUEST) AS G
    ON ROOM_NUMBER=GUEST_NUMBER

    我们已经看到，在这种情况下使用 ROW_NUMBER() 函数可以为我们的开发节省很多力气。我们不必打开两个游标并迭代它们。

    使用 ROW_NUMBER() OVER(PARTITION ... ) 将不抽烟的客人分配到“无烟”房间

    前一章的示例过于简单。这里让我们更接近现实一点。让我们确保吸烟的客人住进允许吸烟的房间，而不吸烟的客人则住进无烟房间。下面的查询就实现了这一点： 

   SELECT ROOM_NUMBER, GUEST_NUMBER, SMOKER, SMOKING, ROOM_ID, GUEST_ID
    FROM
    (SELECT ROW_NUMBER() OVER(PARTITION BY SMOKING ORDER BY ROOM_ID)
 AS ROOM_NUMBER, ROOM_ID, SMOKING FROM ROOM) AS R
    JOIN
    (SELECT ROW_NUMBER() OVER(PARTITION BY SMOKER ORDER BY GUEST_ID)
 AS GUEST_NUMBER, GUEST_ID, SMOKER FROM GUEST) AS G
    ON ROOM_NUMBER=GUEST_NUMBER AND SMOKER=SMOKING
    ROOM_NUMBERGUEST_NUMBERSMOKER SMOKING ROOM_IDGUEST_ID
    -------------------- -------------------- ------ -------
    11 NN1392
    22 NN142321
    33 NN202444
    11 YY12117
    22 YY20157

    同样，如果理解了如何联结 GUEST 和 ROOM 表中的记录，就可以通过一条简单的 INSERT 语句填充 GUEST_ASSIGNMENT 表。这种方法显然比迭代客人上的游标和房间上的游标要容易得多。