The task is not practical. I want to evaluate what approaches it can be solved on SQL'yah different dialects ( PostgreSQL , SQLite3 , MySQL ). And how difficult it can be solved.

Task

There is a table with positive numbers [0..N] . It is necessary to derive in ascending order all the numbers from the specified table, which are included in the Fibonacci sequence .

    2 answers 2

    Generator without using tables for SQLite, postgresql:

     WITH Recursive Q(Num,Prev) as( select 1,1 union all select Q.Num+Q.Prev,Q.Num from Q where Q.Num<10000 ) select Num from Q

    If you need to select from them only those numbers that are in a certain table - then add the join to this final select with this table to check for the presence of a number in it.

    For MS SQL and Oracle, remove the phrase Recursive from the query above. For Oracle, add an additional from DUAL after select 1,1 .

    MySQL requires a reference table with the required number of records, the contents of these records do not matter:

     select @tmp:=@Prev+@Num as Num, @Prev:=@Num, @Num:=@tmp from seqnum, (select @Prev:=1, @Num:=1) A order by Num

    This is again a generator. To check the presence in a certain table, enclose it in a subquery and join.

    • And once again senks! I’m still accepting the question without checking, for the holiday has begun))) And then I’m sure to sort out. The topic of recursion is unknown to me, most of all it attracts to see EXPLAIN of this economy. All the best in the new year!))) - Majestio
    • one
      A few words to protect MySQL. In MySQL8 / MariaDB10.2, there will also be WITH RECURSIVE. For 8-ki you can already feel. Here - sqlinfo.ru/articles/info/27.html - translation of one of the articles on this topic with just an example of the search for Fibonacci numbers. Holiday greetings! - retvizan

    I answer myself :) for the time being for PostgreSQL :

    It is solved in several stages:

    1) Search for the maximum value in the table

    2) CROSS JOIN entire table with the result of the sample from the stored procedure by correspondence

     CREATE OR REPLACE FUNCTION fib(f INTEGER) RETURNS SETOF INTEGER LANGUAGE SQL AS $$ WITH RECURSIVE t(a,b) AS ( VALUES(0,1) UNION ALL SELECT greatest(a,b), a + b AS a FROM t WHERE b < $1 ) SELECT a FROM t; $$;

    where the argument will be the maximum value from the table

    For SQLite3 and MySQL, we are waiting for c ...) Although, to be honest, perhaps for PostgreSQL without a stored procedure.