需求:
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;