獲取多行相當于兩個欄位上的除法-有解無憂

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

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

我已經嘗試過，但沒有解決方案來達到這個結果。誰能幫我搞定？

uj5u.com熱心網友回復：

遞回陳述句或使用數字表的陳述句可能是前兩個選項：

桌子：

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

uj5u.com熱心網友回復：

假設名為 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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/411239.html

標籤：

上一篇：如何根據位置ID和名稱從表中洗掉重復項

下一篇：喜歡香蕉不喜歡蘋果的客戶-改進查詢