在一个firebird过程中显示两个没有相互字段的无关select查询

假设您想要一个由不相关的集合组成的结果集被“压缩”起来,那么您应该为任一表中的每一行分配一个行号(使用firebird3的 row_number() )然后在此基础上进行连接(使用外部连接,所以哪一个行更多并不重要)。

这将导致如下查询:

select a.number, b.letter
from (select row_number() over() as t1nr, number from table1) a
full outer join (select row_number() over() as t2nr, letter from table2) b
    on a.t1nr = b.t2nr

注意,根据您的需要,您可能需要为 ,例如 row_number() over(order by number) 和 row_number() over(order by letter) .

或者,您可以使用 CURSOR

numbers 和 letters 而不是 table1 table2

execute block returns (number integer, letter char(1))
as
  declare cur_numbers cursor for (select number from numbers);
  declare cur_letters cursor for (select letter from letters);
  declare all_numbers_fetched boolean = false;
  declare all_letters_fetched boolean = false;
begin
  open cur_numbers;
  open cur_letters;
  while (true) do
  begin

    if (not all_numbers_fetched) then
    begin
      -- fetch a single row from numbers
      fetch cur_numbers into :number;
      if (row_count = 0) then
      begin
        -- all numbers fetched
        close cur_numbers;
        all_numbers_fetched = true;
        number = null;
      end
    end

    if (not all_letters_fetched) then
    begin
      -- fetch a single row from letters
      fetch cur_letters into :letter;
      if (row_count = 0) then
      begin
        -- all letters fetched
        close cur_letters;
        all_letters_fetched = true;
        letter = null;
      end
    end

    if (all_numbers_fetched and all_letters_fetched) then
      leave;

    suspend;
  end
end