如何查询Trip表中所有非NULL的座位信息（关联Book表）

嘿，我来帮你搞定这个座位信息查询的问题！首先得提个小问题：你给出的Trip表里面有重复的列名（seat1和seat11各出现了两次），这在实际数据库里是不允许存在的，我先假设这是输入时的笔误，把列名调整成seat1到seat11唯一的状态来写解决方案哈。

你的需求是展示所有值不为NULL的座位信息，核心思路是把Trip表中横向存储的座位列转换成纵向的行记录，然后过滤掉状态为NULL的座位，再关联Book表获取完整的订单和行程信息。下面分几种常用数据库给出具体实现：

1. MySQL 实现 #

MySQL没有内置的行转列函数，我们可以用UNION ALL来逐个拆分座位列：

SELECT 
    b.bookID,
    t.tripNo,
    s.seat_name,
    s.seat_status
FROM Book b
JOIN Trip t ON b.bookID = t.bookID AND b.tripNo = t.tripNo
JOIN (
    -- 逐个将座位列转为行，同时过滤NULL值
    SELECT tripNo, 'seat1' AS seat_name, seat1 AS seat_status FROM Trip WHERE seat1 IS NOT NULL
    UNION ALL
    SELECT tripNo, 'seat2' AS seat_name, seat2 AS seat_status FROM Trip WHERE seat2 IS NOT NULL
    UNION ALL
    SELECT tripNo, 'seat3' AS seat_name, seat3 AS seat_status FROM Trip WHERE seat3 IS NOT NULL
    UNION ALL
    SELECT tripNo, 'seat4' AS seat_name, seat4 AS seat_status FROM Trip WHERE seat4 IS NOT NULL
    UNION ALL
    SELECT tripNo, 'seat5' AS seat_name, seat5 AS seat_status FROM Trip WHERE seat5 IS NOT NULL
    UNION ALL
    SELECT tripNo, 'seat6' AS seat_name, seat6 AS seat_status FROM Trip WHERE seat6 IS NOT NULL
    UNION ALL
    SELECT tripNo, 'seat7' AS seat_name, seat7 AS seat_status FROM Trip WHERE seat7 IS NOT NULL
    UNION ALL
    SELECT tripNo, 'seat8' AS seat_name, seat8 AS seat_status FROM Trip WHERE seat8 IS NOT NULL
    UNION ALL
    SELECT tripNo, 'seat9' AS seat_name, seat9 AS seat_status FROM Trip WHERE seat9 IS NOT NULL
    UNION ALL
    SELECT tripNo, 'seat10' AS seat_name, seat10 AS seat_status FROM Trip WHERE seat10 IS NOT NULL
    UNION ALL
    SELECT tripNo, 'seat11' AS seat_name, seat11 AS seat_status FROM Trip WHERE seat11 IS NOT NULL
) s ON t.tripNo = s.tripNo
ORDER BY b.bookID, s.seat_name;

2. SQL Server 实现 #

SQL Server支持UNPIVOT语法，可以更简洁地完成行转列：

SELECT 
    b.bookID,
    t.tripNo,
    s.seat_name,
    s.seat_status
FROM Book b
JOIN Trip t ON b.bookID = t.bookID AND b.tripNo = t.tripNo
UNPIVOT (
    -- 指定要提取的值列和列名对应的列
    seat_status FOR seat_name IN (seat1, seat2, seat3, seat4, seat5, seat6, seat7, seat8, seat9, seat10, seat11)
) s
WHERE s.seat_status IS NOT NULL
ORDER BY b.bookID, s.seat_name;

3. PostgreSQL 实现 #

PostgreSQL可以用LATERAL JOIN结合VALUES来直观地拆分列，这种方法可读性很强：

SELECT 
    b.bookID,
    t.tripNo,
    s.seat_name,
    s.seat_status
FROM Book b
JOIN Trip t ON b.bookID = t.bookID AND b.tripNo = t.tripNo
JOIN LATERAL (
    -- 把每个座位列转为一行记录
    VALUES
        ('seat1', t.seat1),
        ('seat2', t.seat2),
        ('seat3', t.seat3),
        ('seat4', t.seat4),
        ('seat5', t.seat5),
        ('seat6', t.seat6),
        ('seat7', t.seat7),
        ('seat8', t.seat8),
        ('seat9', t.seat9),
        ('seat10', t.seat10),
        ('seat11', t.seat11)
) s(seat_name, seat_status) ON s.seat_status IS NOT NULL
ORDER BY b.bookID, s.seat_name;

注意事项 #

• 一定要先修正Trip表的重复列名，否则数据库无法创建或正常操作这张表；
• 以上查询会返回所有已被预订（状态为booked）的座位，同时关联对应的bookID和tripNo，方便你查看每个订单对应的预订座位。

内容的提问来源于stack exchange，提问作者whalesboy