1

How to have this code/output in MySql:

Had a recursive cte in MSSQL to fill a table with random data without loop e.g begin/end. Searched for similar logic in MySql but most or all solutions were using begin/end or for loops. Wonder if you could suggest a solution without loop in MySql.

Thanks 

--MSSQL cte:------------------------------------

with t1( idi,val ) as
(
  select 
  idi=1
  ,val=cast( 1 as real)

  union all

  select
  idi=idi+1
  ,val=cast(val+rand() as real)

  from t1
  where idi<5
)
select idi,val from t1

-----------------------------------------------

Output in MSSQL:( semi random values)

idi  |  val  
-------------
1    | 1  
2    | 1.11  
3    | 1.23  
4    | 1.35  
5    | 1.46

Edit:

Regarding discussions which considers set based codes as loop based codes indeed, I could understand this but just out of interest gave it a try in MSSQL 2008r2, here is the result:

1- above code with 32000 recursion took 2.812 sec

2- above output created with WHILE BEGIN END loop for 32000 took 53.640 sec

Obviously this is a big difference in execution time.

Here is the loop based code:

  insert into @t1(idi,val)
  select 
     idi=1
     ,val=1

  declare @ii  int = 2
  while @ii<32000
  begin

  insert into @t1(idi,val)
  select 
       idi=idi+1
       ,val=val+rand()

  from @t1
  where idi=@ii-1
  set @ii=@ii+1
  end

  select * from @t1
Hamidra
  • 13
  • 3
  • Why does it matter? Recursive CTEs generally perform equal or worse than procedural SQL loops. – Bacon Bits Nov 18 '14 at 06:05
  • Searched n saw such discussion which 'Set based codes are a loop based code in depth..' thanks http://stackoverflow.com/questions/7825820/is-a-sql-server-recursive-cte-considered-a-loop – Hamidra Nov 18 '14 at 06:18
  • @Bacon, I agree with the fact indeed but how you explain such difference in execution time as above trial? tnx – Hamidra Nov 18 '14 at 08:16

1 Answers1

0

MySql doesn't support CTE.

You need a procedure or some tricky queries like this one:

set @id=0;
set @val=0;

SELECT @id:=@id+1 As id,
       @val:=@val+rand() As val
FROM information_schema.tables x
CROSS JOIN information_schema.tables y
LIMIT 10
krokodilko
  • 35,300
  • 7
  • 55
  • 79