SQL 表連接去重難題：訂單-人員-房間多對多映射缺失關聯鍵導致無法精確分配

問題場景

兩表關聯查詢：

• 表 A (訂單-入住人)：訂單號、入住人 (一訂單多入住人)
• 表 B (訂單-房間號)：訂單號、房間號 (一訂單多房間)

期望結果：每行一個入住人對應一個房間號 (如張1→201, 王1→202)。

核心結論：數據模型缺失「人-房」關聯鍵，無法通過 SQL 確定精確映射

詳細分析

-- 表 A
OrderID | GuestName
1       | 張1
1       | 王1
2       | 張2
3       | 張3

-- 表 B
OrderID | RoomNo
1       | 201
1       | 202

訂單 1 有 2 位入住人、2 個房間。但沒有任何字段記錄「張1住201、王1住202」或反過來。

嘗試方案及其缺陷

正確的數據模型設計

-- 核心關聯表：記錄每個入住人的具體房間分配
CREATE TABLE order_guest_room (
    order_id INT,
    guest_name VARCHAR(50), -- 或 guest_id 關聯客人主檔
    room_no VARCHAR(20),
    check_in_time DATETIME,
    PRIMARY KEY (order_id, guest_name) -- 或 guest_id
);

-- 查詢變為簡單直觀
SELECT og.order_id, og.guest_name, og.room_no
FROM order_guest_room og
WHERE og.order_id = 1;

若無法修改表結構 (歷史系統/只讀庫)

1. 業務規則約定：如「入住人姓名筆劃順序對應房間號升序」等人為規則，在 SQL 中用 `ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY GuestName)` 和 `ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY RoomNo)` 聯結。但必須經業務方確認規則真實有效。
2. 外部數據源補全：從入住登記紙質單、前台系統導出映射表臨時導入關聯。
3. 展示層妥協：前端顯示「訂單 1: 張1、王1 | 房間: 201、202」不做逐行強綁定，備註「具體分配請查閱登記單」。

總結

SQL 無法憑空創造不存在的業務關係。**「一訂單多入住人、多房間」若無「人-房」映射字段/表，精確分配在邏輯上是不可判定的**。解決路徑：補齊數據模型 (加關聯表/字段) → 修改業務錄入流程 → 歷史數據補錄，而非硬寫 SQL 強行分配。