Printing a given number sequence in mysql-CodePudding

I want to print number sequence like following in mysql: 1 2 3 1 2 3 1 2 3 . . to 16 times

I am trying this:

select (t*16 1) x from
(select 0 t union select 1 union select 2 ) A
,
(select 0 u union select 1 union select 2 )B
order by x;

CodePudding user response：

WITH RECURSIVE
cte AS (
    SELECT 1 num
    UNION ALL
    SELECT num   1 FROM cte WHERE num < 16
)
SELECT t1.num
FROM cte t1
CROSS JOIN cte t2
WHERE t1.num <= 3
ORDER BY t2.num, t1.num;

For versions without window functions such as 5.7 I wonder if we can do it using a single statement ? – blabla_bingo

SELECT t0.id
FROM ( SELECT 1 id UNION ALL SELECT 2 UNION ALL SELECT 3 ) t0
CROSS JOIN ( SELECT 1 id UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) t1
CROSS JOIN ( SELECT 1 id UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) t2
ORDER BY t1.id, t2.id, t0.id