SQL中如何实现单值转多行输出？家具表场景求更优方案

我有一个FURNITURE表，结构和数据如下：

CREATE TABLE FURNITURE
       (ROOM VARCHAR2(50) CONSTRAINT PK_DEPT PRIMARY KEY,
        APPLIANCE VARCHAR2(50));
INSERT INTO FURNITURE VALUES
        ('BEDROOM','BED');
INSERT INTO FURNITURE VALUES
        ('LIVING ROOM', 'SOFA');
INSERT INTO FURNITURE VALUES
        ('TV ROOM', 'TV');
INSERT INTO FURNITURE VALUES
        ('ALL', 'LAMP');

需要得到如下输出（排序无关）：

我目前的实现方法如下：

select f1.room,
       f1.appliance
  from furniture f1 where room <> 'ALL'
 union
select all_rooms.room,
       f2.appliance
  from furniture f2
cross join
       (select distinct room from furniture where room <> 'ALL') all_rooms
 where f2.room = 'ALL';

有没有更优的解决方案？我尝试用CROSS APPLY解决但没成功。

更优解决方案 #

方案1：简化原逻辑，提升性能

原方案中UNION会触发去重操作，而我们的两个查询结果不会有重复，改用UNION ALL可以避免不必要的去重开销；同时因为ROOM是主键，子查询不需要DISTINCT：

SELECT f1.room, f1.appliance
FROM FURNITURE f1
WHERE f1.room != 'ALL'
UNION ALL
SELECT r.room, f2.appliance
FROM FURNITURE f2
JOIN (SELECT room FROM FURNITURE WHERE room != 'ALL') r
ON f2.room = 'ALL';

方案2：用CROSS JOIN一次性生成结果（更简洁）

通过CROSS JOIN生成所有非"ALL"房间与所有设备的组合，再筛选出符合要求的行，逻辑更直观：

SELECT r.room, f.appliance
FROM (SELECT room FROM FURNITURE WHERE room != 'ALL') r
CROSS JOIN FURNITURE f
WHERE f.room = r.room OR f.room = 'ALL';

补充：关于CROSS APPLY

在Oracle中，CROSS APPLY适用于关联子查询，这里可以用它来关联每个房间和"ALL"设备，结合原有房间设备得到结果：

SELECT r.room, a.appliance
FROM (SELECT room FROM FURNITURE WHERE room != 'ALL') r
CROSS APPLY (
    SELECT appliance FROM FURNITURE WHERE room = r.room
    UNION ALL
    SELECT appliance FROM FURNITURE WHERE room = 'ALL'
) a;

这个写法通过CROSS APPLY为每个房间单独获取其专属设备和"ALL"设备，同样能得到目标结果。

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