如何使其具有动态性？
您需要知道每个航班的出口座位在哪里。这可能是存储在另一个表中的数据，也可能是从外部源获取的数据（然后您应该问为什么它没有存储在表中）。不要在每次运行查询时都试图动态地将退出席位传递到查询中;只需将这些信息存储在一个表中，因为航空公司不太可能为每次飞行重新配置飞机上的座位，因此您只需为每架飞机设置一个标准配置即可（或者甚至是每个机身模型/航空母舰组合，因为它们可能使用标准化布局）。
假设您有一个列出每个特定航班的所有座位的表（如果没有，则创建该表），则可以添加一列，说明某个座位是否属于一组出口座位：

CREATE TABLE table_listing_all_seats_per_flight (
  id NUMBER(10,0)
     GENERATED ALWAYS AS IDENTITY
     PRIMARY KEY,
  flight_number   VARCHAR2(8),
  seat_number     VARCHAR2(4) CHECK (REGEXP_LIKE(seat_number, '\d{1,3}[A-Z]')),
  exit_seat_group VARCHAR2(10)
                  CHECK (exit_seat_group IS NULL
                        OR REGEXP_LIKE(exit_seat_group, '\d{1,3}[A-Z]{1,7}')),
  UNIQUE (flight_number, seat_number)
);

字符串
然后，您可以使用以下命令查找未分配的出口座位：

SELECT flight_number,
       flight_date,
       LISTAGG(exit_seat_group, ', ')
         WITHIN GROUP (ORDER BY LPAD(exit_seat_group, 10, '0'))
         AS unoccupied_exit_seats
FROM   (
  SELECT s.flight_number,
         s.flight_date,
         a.exit_seat_group
  FROM   table_listing_all_seats_per_flight a
         LEFT OUTER JOIN seat_alloc s
         PARTITION BY (s.flight_number, s.flight_date)
         ON (s.flight_number = a.flight_number AND s.seat_number = a.seat_number)
  WHERE  a.exit_seat_group IS NOT NULL
  GROUP BY
         s.flight_number,
         s.flight_date,
         a.exit_seat_group
  HAVING COUNT(CASE WHEN s.availability_attribute = 'Occupied' THEN 1 END) = 0
)
GROUP BY
       flight_number,
       flight_date;

型
其中，对于样本数据：

CREATE TABLE seat_alloc (
  flight_number,
  seat_number,
  flight_date DATE,
  availability_attribute VARCHAR2(10),
  CONSTRAINT seat_alloc__fn_sn__fk FOREIGN KEY (flight_number, seat_number)
    REFERENCES table_listing_all_seats_per_flight (flight_number, seat_number)
);

INSERT INTO table_listing_all_seats_per_flight (flight_number, seat_number, exit_seat_group)
SELECT 'FN',
       X || Y,
       CASE
       WHEN x IN (4, 8, 12) AND y <= 'C' THEN x || 'ABC'
       WHEN x IN (4, 8, 12) AND y >  'C' THEN x || 'DEF'
       ELSE NULL
       END
FROM   (SELECT LEVEL AS x FROM DUAL CONNECT BY LEVEL <= 12)
       CROSS JOIN
       (SELECT CHR(64 + LEVEL) AS y FROM DUAL CONNECT BY LEVEL <= 6);

INSERT INTO seat_alloc(flight_number, seat_number, flight_date, availability_attribute)
SELECT flight_number,
       seat_number,
       SYSDATE,
       'Occupied'
FROM   table_listing_all_seats_per_flight
WHERE  flight_number = 'FN'
AND    seat_number IN (
         '1A',
          '4B',
          '5C', '5D',
          '8A', '8B', '8C', '8D', '8E', '8F',
          '12F'
        );

型
产出：
| 航班_编号|航班_日期|未占用_出口_座位|
| --|--|--|
| FN| 2023年10月30日上午10：11：59| 4DEF、12 ABC标准配置|
fiddle

计算座位组。
将座位编号转换为行和列，然后您可以找到每行的最大列值。由于无法指定实际的座位组，因此如果您假设座位将位于左侧或右侧座位组中，并且左侧座位组将位于座位列下半部分的列中，从最大列值开始测量，并且正确的座位组将位于列的上半部分，同样基于最大值，然后您可以使用该值将各个座位分配给组。
考虑到所有这些，您可以聚合并使用HAVING子句来过滤没有占用座位的座位组：

SELECT flight_number,
       flight_date,
       seat_row || LISTAGG(SUBSTR(seat_number, -1))
                     WITHIN GROUP (ORDER BY seat_column)
         AS unoccupied_exit_seats
FROM   (
  SELECT flight_number,
         flight_date,
         seat_number,
         availability_attribute,
         seat_row,
         seat_column,
         CASE
         WHEN seat_column * 2
              <= MAX(seat_column) OVER (
                   PARTITION BY flight_number, flight_date, seat_row
                 )
         THEN 'L'
         ELSE 'R'
         END AS seat_group
  FROM   (  
    SELECT flight_number,
           flight_date,
           seat_number,
           availability_attribute,
           SUBSTR(seat_number, 1, LENGTH(seat_number) - 1) AS seat_row,
           ASCII(SUBSTR(seat_number, -1)) - 64 AS seat_column
    FROM   seat_alloc
    WHERE  location_attributes LIKE '%E%'
  )
)
GROUP BY
       flight_number,
       flight_date,
       seat_row,
       seat_group
HAVING COUNT(CASE availability_attribute WHEN 'Occupied' THEN 1 END) = 0;

字符串
其中，对于样本数据：

CREATE TABLE seat_alloc (
  flight_number          VARCHAR2(10),
  seat_number            VARCHAR2(4),
  flight_date            DATE,
  availability_attribute VARCHAR2(10),
  location_attributes    VARCHAR2(10)
);

INSERT INTO seat_alloc(
  flight_number,
  seat_number,
  flight_date,
  availability_attribute,
  location_attributes
)
SELECT 'FN',
       x || y,
       SYSDATE,
       CASE
       WHEN (x, y) IN (
              (1, 'C'),
              (4, 'B'),
              (8, 'A'), (8, 'B'), (8, 'C'), (8, 'D'), (8, 'E'), (8, 'F'),
              (12, 'D'), (12, 'E'), (12, 'F')
            )
       THEN 'Occupied'
       ELSE 'Unoccupied'
       END,
       CASE
       WHEN x IN (4, 8, 12) THEN 'E'
       ELSE NULL
       END
FROM   (SELECT LEVEL AS x FROM DUAL CONNECT BY LEVEL <= 12)
       CROSS JOIN
       (SELECT CHR(64 + LEVEL) AS y FROM DUAL CONNECT BY LEVEL <= 6);

型
产出：
| 航班号|航班日期|出口处座位|
| --|--|--|
| FN| 2023-10-30 13：00：43| 4DEF|
| FN| 2023-10-30 13：00：43| 12ABC|
fiddle