我的資料格式為：
重現：
DROP TABLE IF EXISTS SALARY;
CREATE TEMP TABLE salary
(
Employee varchar(100),
Salary1 numeric(38,12),
Salary2 numeric(38,12)
);
INSERT INTO salary (Employee, Salary1 ,Salary2)
VALUES ('A1',100,300),('A2',200,300),('A3',300,450),('A4',400,600);
我想將其平均劃分（因為我們將 2 天的工資資料匯總到 1 列中）并將其轉換為每日級別的資料，如下所示：
因此，如果您看到員工 A2-第 3 和第 4 的工資總和可能為 300（來自第 2 個表的 150 150）。任何幫助/線索表示贊賞。
uj5u.com熱心網友回復：
具有所需日期的具體化日歷表將有助于生成查詢所需的日期。如果沒有一個，計數表或 CTE（如下例所示）是一種替代方法。
DECLARE
@StartDate date = '2022-05-01'
, @DaysPerSalary int = 2;
WITH
t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
,tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
,salary1 AS (
SELECT Employee, Salary1 / @DaysPerSalary AS Salary
FROM SALARY
)
,salary2 AS (
SELECT Employee, Salary2 / @DaysPerSalary AS Salary
FROM SALARY
)
SELECT DATEADD(day, tally.num-1, @StartDate), Employee, Salary
FROM tally
CROSS JOIN salary1
WHERE tally.num <= @DaysPerSalary
UNION ALL
SELECT DATEADD(day, tally.num-1 @DaysPerSalary, @StartDate), Employee, Salary
FROM tally
CROSS JOIN salary2
WHERE tally.num <= @DaysPerSalary
ORDER BY Employee, Salary;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/476572.html