时间戳

首页 > 解决方案 > 如何使用 SELECT 语句中的可用值更新表

sql-server - 如何使用 SELECT 语句中的可用值更新表

问题描述

概述

我正在尝试使用 SQL 服务器上的存储过程来解决“分配”问题，而不是在我们的 Access 应用程序中使用 VBA 代码。该问题类似于将可用的航班座位分配给机票，如此SQL Fiddle所示

表

航班

+----------+--------------+
| FlightID | FlightNumber |
+----------+--------------+
|        1 | L1044        |
|        2 | T553         |
|        3 | AC1105       |
+----------+--------------+

座位（针对 FlightID 2 过滤）

+--------+----------+------------+
| SeatID | FlightID | SeatNumber |
+--------+----------+------------+
|    104 |        2 | F21        |
|    105 |        2 | A22        |
|    106 |        2 | B33        |
+--------+----------+------------+

机票（针对 FlightNumber T553 过滤）

+----------+--------------+--------+
| TicketID | FlightNumber | SeatID |
+----------+--------------+--------+
|     5003 | T553         | (null) |
|     5005 | T553         | (null) |
|     5006 | T553         | (null) |
|     5007 | T553         | (null) |
+----------+--------------+--------+

问题

给定一个特定的FlightNumber，我如何更新表格以使用表格中的可用值Ticket填充，请记住每个座位 ID 只能使用一次。此外，如果可能的话，我希望能够在交易中做到这一点。SeatIDSeat

预期结果：

+----------+--------------+--------+
| TicketID | FlightNumber | SeatID |
+----------+--------------+--------+
|     5003 | T553         | 104    |
|     5005 | T553         | 105    |
|     5006 | T553         | 106    |
|     5007 | T553         | (null) |
+----------+--------------+--------+

标签： sql-server

解决方案

您可以为派生表中的每张票和每个座位分配行号，加入行号，然后复制SeatID. 行编号确保只有一个座位被分配给一张票。

我建议您为每个表选择一个合适的排序列。

UPDATE t
SET SeatID = s.SeatID 
FROM (
    SELECT *,
        rn = ROW_NUMBER() OVER (PARTITION BY t.FlightNumber ORDER BY t.TicketID)
    FROM Ticket t
    WHERE t.FlightNumber = 'T553'
) t
JOIN Flight f ON f.FlightNumber = t.FlightNumber
JOIN (
    SELECT *,
        rn = ROW_NUMBER() OVER (PARTITION BY s.FlightID ORDER BY s.SeatID)
    FROM Seat s
) s ON s.FlightID = f.FlightID AND s.rn = t.rn;

推荐阅读