Display two unrelated select queries with no mutua

问题:

How to properly display two unrelated select queries with no mutual fields in on procedure?

Table1
        Number
          1
          2
          3
          4
          5

Table2
        Letter
          a

When i try to call them using this procedure,

CREATE PROCEDURE SAMPLE
RETURNS(
  Number SMALLINT,
  Letter Varchar)
AS
BEGIN
  FOR
    SELECT
      A.Number,
      B.Letter
    FROM Table1 A, Table2 B
    INTO
      :Number,
      :Letter
  DO
    BEGIN
      SUSPEND;
    END
END;

i get this result

Number Letter
  1      a
  2      a
  3      a
  4      a
  5      a

here is my desired result

   Number Letter
      1      a
      2      
      3      
      4      
      5      

回答1:

Assuming you want a result set of unrelated sets 'zipped' up, you should assign each row from either table with a row number (using Firebird 3's row_number()) and then join on that (using an outer join, so it doesn't matter which has more rows).

This would result in a query that looks like:

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

Note, depending on your needs, you may want to specify an explicit order for row_number(), for example row_number() over(order by number) and row_number() over(order by letter).

Alternatively, you could use the CURSOR support in Firebird's stored procedures and manual control the iteration, this gets quite messy and hard to follow though.

You could do something like I show below (I'm using Firebird 3, but replacing those booleans with a smallint and using 1 and 0 should work with Firebird 2.0 - 2.5). For readability I used table names numbers and letters instead of table1 and 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