在一个firebird过程中显示两个不相关字段的互不相关的选择查询 [英] Display two unrelated select queries with no mutual fields in one firebird procedure

问题描述

如何正确显示两个不相关的选择查询，且在过程中没有相互的字段?

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;

我得到这个结果

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

这是我想要的结果

   Number Letter
      1      a
      2      
      3      
      4      
      5      

推荐答案

假设您想要将不相关的集合的结果集压缩"，则应为任一表中的每一行分配一个行号(使用Firebird 3的row_number() )，然后加入该连接(使用外部连接，因此具有更多行无关紧要.)

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).

这将导致查询如下:

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()指定一个明确的顺序，例如row_number() over(order by number)和row_number() over(order by letter).

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).

或者，您可以在Firebird的存储过程中使用CURSOR支持，并手动控制迭代，但这会变得非常混乱且难以理解.

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.

您可以执行如下所示的操作(我正在使用Firebird 3，但是将这些布尔值替换为smallint并使用1和0应该可以与Firebird 2.0-2.5一起使用).为了提高可读性，我使用表名numbers和letters代替了table1和table2

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

这篇关于在一个firebird过程中显示两个不相关字段的互不相关的选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！