查询记录变量号

Question

我的表;

ID- PRODUCT - PIECES
1 - BANANA  -   2
2 - APPLE   -   3
3 - ORANGE  -   1
4 - PEACH   -   2
5 - CHERRY  -   2
6 - STRAWBERRY- 4

我想要这个结果;

ID- PRODUCT - PIECES
1 - BANANA  -   2
1 - BANANA  -   2
2 - APPLE   -   3
2 - APPLE   -   3
2 - APPLE   -   3
3 - ORANGE  -   1
4 - PEACH   -   2
4 - PEACH   -   2
5 - CHERRY  -   2
5 - CHERRY  -   2
6 - STRAWBERRY- 4
6 - STRAWBERRY- 4
6 - STRAWBERRY- 4
6 - STRAWBERRY- 4  

我该怎么做？

Answer 1

在我的头顶上：

WITH cte AS (
SELECT *, 1 AS level FROM yourTable UNION ALL
SELECT *, 2 FROM yourTable UNION ALL
SELECT *, 3 FROM yourTable UNION ALL
SELECT *, 4 FROM yourTable
-- add more queries for higher levels
)
SELECT
ID,
PRODUCT,
PIECES
FROM cte
WHERE
PIECES >= level
ORDER BY
ID;

在SQL Server或MySQL 8+中，我们可以使用递归CTE而不是上面的硬编码版本来处理任意数量的记录重复。

Answer 2

假设 SQL Server。

rCTE：

--Sample data
WITH VTE AS(
SELECT *
FROM (VALUES(1,'BANANA',2),
(2,'APPLE',3),
(3,'ORANGE',1),
(4,'PEACH',2),
(5,'CHERRY',2),
(6,'STRAWBERRY',4))V(ID, Product,Pieces)),
--solution
rCTE AS(
SELECT V.ID,
V.Product,
V.Pieces,
1 AS Piece
FROM VTE V
UNION ALL
SELECT r.ID,
r.Product,
r.Pieces,
r.Piece +1
FROM VTE V
JOIN rCTE r ON V.ID = r.ID
AND V.Pieces > r.Piece)
SELECT ID,
Product,
Pieces
FROM rCTE
ORDER BY ID, Piece;

理 货：

--Sample data
WITH VTE AS(
SELECT *
FROM (VALUES(1,'BANANA',2),
(2,'APPLE',3),
(3,'ORANGE',1),
(4,'PEACH',2),
(5,'CHERRY',2),
(6,'STRAWBERRY',4))V(ID, Product,Pieces)),
N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT TOP (SELECT MAX(Pieces) FROM VTE)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2) --100 rows, Add more as needed
SELECT V.ID,
V.Product,
V.Pieces
FROM VTE V
JOIN Tally T ON V.Pieces >= T.I
ORDER BY V.ID,
T.I;

如评论中所述，我建议使用计数，特别是如果PIECES的值要大得多。

Answer 3

从这里，

创建函数

CREATE FUNCTION NumbersTable (
@fromNumber int,
@toNumber int,
@byStep int
)
RETURNS @NumbersTable TABLE (i int)
AS
BEGIN
WITH CTE_NumbersTable AS (
SELECT @fromNumber AS i
UNION ALL
SELECT i + @byStep
FROM CTE_NumbersTable
WHERE
(i + @byStep) <= @toNumber
)
INSERT INTO @NumbersTable
SELECT i FROM CTE_NumbersTable OPTION (MAXRECURSION 0)
RETURN;
END

并使用交叉应用，

create table #tab(Id int,PRODUCT varchar(200), PIECES int);
insert into #tab VALUES(1,'BANANA',2),
(2,'APPLE',3),
(3,'ORANGE',1),
(4,'PEACH',2),
(5,'CHERRY',2),
(6,'STRAWBERRY',4)
create table #tab2(Id int,PRODUCT varchar(200), PIECES int);
INSERT INTO #tab2(Id, PRODUCT,PIECES)
SELECT Id, PRODUCT,PIECES
FROM #tab t1
CROSS APPLY dbo.NumbersTable(1,t1.PIECES,1)
select * from #tab2