You need to write a sql query that would return exactly this output:

id| --- 1 | 2 | 3 | 5 |

Imagine that there are no tables in the database and you cannot create them.

 mysql> SELECT 1 id UNION SELECT 2 id UNION SELECT 3 id UNION SELECT 5 id; +----+ | id | +----+ | 1 | | 2 | | 3 | | 5 | +----+ 4 rows in set (0,00 sec)

Is there a more elegant solution?

  • order by forgot. - i-one
  • one
    In MySQL for 5 numbers there is no more elegant one. If there are more numbers, then it makes sense to join from a couple of similar queries to multiply the number of numbers. More beautiful solutions are available only in other DBMS supporting for example CTE. The only improvement is that id can be written only on the first number in the union - Mike
  • well, why, there are procedures - splash58
  • @ splash58 Well, the procedure is not exactly a "sql query", and the TS asks to "write a SQL query" - Mike
  • Well, he will write a function once, and he will be prompted - splash58

2 answers 2

In MySQL, the only option (Alias ​​is needed only for the first number):

 select 1 ID union select 2 union select 3 union select 5

The most beautiful (for a small number of numbers) version can be written in MS-SQL and Postrgess :

 select * from (values (1),(2),(3),(5)) as t(id)

Oracle (Using system type as collection):

 select column_value ID from table(sys.odcinumberlist(1,2,3,5))

If there are much more numbers and they are just running in a row, then it is almost universal (Of the widely used DBMS, it does NOT work only in MySQL):

 with Q as ( select 1 ID union all select ID+1 from Q where ID<5 ) select * from Q

The most concise (IMHO) option for a large number of numbers in a row in Oracle :

 select rownum id from DUAL connect by rownum<6

    There is such an option using T-SQL. Physically, the table is not created, so the condition is fulfilled. Plus, this solution is much more flexible, if suddenly someone decides to change the task.

     declare @t1 as table(ID int) declare @i int=0 while (@i<5) begin set @i+=1 if (@i<>4) insert into @t1 values(@i) end select * from @t1
    • one
      And about the T-SQL speech did not go. And on MS-SQL without any variables, it is with “one request” that it is written with Q as ( select 1 ID union all select ID+1 from Q where ID<5 ) select * from Q where ID!=4 And at the same time it works on all others CTE Supports CTE - Mike