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;
Add a comment
|
1 Answer
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
4 Comments
blabla_bingo
That's a good answer for 8.0. For versions without window functions such as 5.7 I wonder if we can do it using a single statement ? Currently querying a temporary table which is populated by iteration of INSERT statements comes to my mind.
Akina
@blabla_bingo When OP does not specify the version we may assume that actual one is used. For versions without window functions such as 5.7 I wonder if we can do it using a single statement ? Of course.
blabla_bingo
Thanks for the answer,Akina. Though it's not as pretty as the one using window function, it does get the job done in one go. Besides, using cross join to build a 4 x 4 table is really smart in this case. Personally, I think I'll resort to a procedure with iteration for simple readability and better control. But still I thank you for your effort.
blabla_bingo
By the way, if the OP wanted to repeat 17 times, I just tested out we could built a 4 x 5 table and use a
LIMIT 51 clause to limit the output : )