MySQL8之CTE

需求：

shopname  begin_time   end_time
A	         1	          22
B	         18	          20
C	         10	          19

要求输出：

shopname    id
A            1
A            2
A            3
A          22-1

数据准备：

CREATE TABLE test  (
  shopname varchar(100) ,
  begin_time int(11) ,
  end_time int(11) 
) ;

 
INSERT INTO test VALUES ('A', 1, 22);
INSERT INTO test VALUES ('B', 18, 20);
INSERT INTO test VALUES ('C', 10, 19);

解决方案：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, test.shopname from test    
  UNION ALL
  SELECT n + 1, test.shopname FROM cte,test WHERE    n < (test.end_time - test.begin_time) 
)
 SELECT * from (
SELECT distinct cte.* FROM cte )g order by g.shopname asc;