获取相当于两个字段上的除法的多行

Question

我有一个包含以下列的表：

Date       Quantity RollCount PlanID
20-12-2021 200      2         100
21-12-2021 150      4         101

我想获得以下数据：

Date        Quantity  RollNo  PlanID
20-12-2021  100       1       100
20-12-2021  100       2       100
21-12-2021  37.5      1       101
21-12-2021  37.5      2       101
21-12-2021  37.5      3       101
21-12-2021  37.5      4       101

我已经尝试过了，但没有得到实现这个结果的解决方案。有人能帮我拿吗？

Answer 1

递归语句或使用数字表的语句可能是前两个选项：

表：

SELECT *
INTO Data
FROM (VALUES
(CONVERT(date, '20-12-2021', 105), 200, 2, 100),
(CONVERT(date, '21-12-2021', 105), 150, 4, 101)
) v (Date, Quantity, RollCount, PlanID) 

带有递归的语句：

; WITH rCTE AS (
SELECT d.Date, d.Quantity, 1 AS RollNo, d.RollCount, d.PlanID
FROM Data d
UNION ALL
SELECT r.Date, r.Quantity, r.RollNo + 1, r.RollCount, r.PlanID
FROM rCTE r
WHERE r.RollNo + 1 <= r.RollCount
)   
SELECT Date, (Quantity * 1.0 / RollCount) AS Quantity, RollNo, PlanID
FROM rCTE
ORDER BY PlanID, RollNo
OPTION (MAXRECURSION 0)

使用数字表(1000行(的语句：

; WITH nCTE AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Rn
FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) a (n)
CROSS APPLY (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) b (n)
CROSS APPLY (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) c (n)
)
SELECT d.Date, (d.Quantity * 1.0 / d.RollCount) AS Quantity, n.Rn AS RollNo, d.PlanID
FROM Data d
JOIN nCTE n ON d.RollCount >= n.Rn
ORDER BY d.PlanID, n.Rn

结果：

Date       Quantity         RollNo PlanID
2021-12-20 100.000000000000 1     100
2021-12-20 100.000000000000 2     100
2021-12-21 37.500000000000  1     101
2021-12-21 37.500000000000  2     101
2021-12-21 37.500000000000  3     101
2021-12-21 37.500000000000  4     101

Answer 2

假设名为tblPlan的表包含列：PlanDate、Quantity、RollCount、PlanID在Oracle中，您可以编写如下查询：

select distinct *
from
(
select PlateDate, Quantiy/RollCount as Quantity, level as RollNo, PlanID
from tblPlan t
connect by level <= t.RollCount
)
order by PlanID, RollNo

编辑：使用CTE:

with cte (PlanDate, Quantity, RollCount, PlanID , RollNo) as (
select t.PlanDate, t.Quantity, t.RollCount, t.PlanID , 1 as RollNo
from tblPlan t

union all

select u.PlanDate, u.Quantity, u.RollCount, u.PlanID , t.RollNo + 1 as RollNo
from cte t
inner join tblPlan u on t.PlanID  = u.PlanID  and t.RollNo < u.RollCount
)
select distinct *
from cte
order by PlanID, RollNo