我有几个月内的产品表及其销售量。
产品月数量2018-01-01 52018-02-01 32018-05-01 5B 2018-08-01 10B 2018-10-01 12...
我想首先填补每个产品的最小日期和最大日期之间的数据差距：
产品月数量2018-01-01 52018-02-01 32018-03-01 02018-04-01 02018-05-01 5B 2018-08-01 10B 2018-09-01 0B 2018-10-01 12...
然后，我需要按月进行每种产品的销售量积累。
产品月total_qty2018-01-01 52018-02-01 82018-03-01 82018-04-01 82018-05-01 13B 2018-08-01 10B 2018-09-01 10B 2018-10-01 22...
我在"交叉加入"子句上摸索，但是它似乎给我带来了一些意外的结果。有人可以帮助提示我如何在SQL中实现这一目标？
预先感谢。
我认为递归CTE是一种简单的方法。代码只是：
with cte as (
select product, min(mon) as mon, max(mon) as end_mon
from t
group by product
union all
select product, dateadd(month, 1, mon), end_mon
from cte
where mon < end_mon
)
select cte.product, cte.mon, coalesce(qty, 0) as qty
from cte left join
t
on t.product = cte.product and t.mon = cte.mon;
这是一个db＆lt;>小提琴。
嗨，我认为此示例可以帮助您并执行您除外的内容：
CREATE TABLE #MyTable
(Product varchar(10),
ProductMonth DATETIME,
Qty int
);
GO
CREATE TABLE #MyTableTempDate
(
FullMonth DATETIME
);
GO
INSERT INTO #MyTable
SELECT 'A', '2019-01-01', 214
UNION
SELECT 'A', '2019-02-01', 4
UNION
SELECT 'A', '2019-03-01', 50
UNION
SELECT 'B', '2019-01-01', 214
UNION
SELECT 'B', '2019-02-01', 10
UNION
SELECT 'C', '2019-04-01', 150
INSERT INTO #MyTableTempDate
SELECT '2019-01-01'
UNION
SELECT '2019-02-01'
UNION
SELECT '2019-03-01'
UNION
SELECT '2019-04-01'
UNION
SELECT '2019-05-01'
UNION
SELECT '2019-06-01'
UNION
SELECT '2019-07-01';
------------- FOR NEWER SQL SERVER VERSION > 2005
WITH MyCTE AS
(
SELECT T.Product, T.ProductMonth AS 'MMonth', T.Qty
FROM #MyTable T
UNION
SELECT T.Product, TD.FullMonth AS 'MMonth', 0 AS 'Qty'
FROM #MyTable T, #MyTableTempDate TD
WHERE NOT EXISTS (SELECT 1 FROM #MyTable TT WHERE TT.Product = T.Product AND TD.FullMonth = TT.ProductMonth)
)
-- SELECT * FROM MyCTE;
SELECT Product, MMonth, Qty, SUM( Qty) OVER(PARTITION BY Product ORDER BY Product
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as 'TotalQty'
FROM MyCTE
ORDER BY Product, MMonth ASC;
DROP TABLE #MyTable
DROP TABLE #MyTableTempDate
我还有其他方法可以在较低的SQL Server版本中执行此操作（例如2005年和较低）如果是您的情况，请选择这是一个选择，我提供了其他一些示例。
您可以使用递归CTE
创建月份DECLARE @MyTable TABLE
(
ProductID CHAR(1),
Date DATE,
Amount INT
)
INSERT INTO @MyTable
VALUES
('A','2018-01-01', 5),
('A','2018-02-01', 3),
('A','2018-05-01', 5),
('B','2018-08-01', 10),
('B','2018-10-01', 12)
DECLARE @StartDate DATE
DECLARE @EndDate DATE
SELECT @StartDate = MIN(Date), @EndDate = MAX(Date) FROM @MyTable
;WITH dates AS (
SELECT @StartDate AS Date
UNION ALL
SELECT DATEADD(Month, 1, Date)
FROM dates
WHERE Date < @EndDate
)
SELECT A.ProductID, d.Date, COALESCE(Amount,0) AS Amount, COALESCE(SUM(Amount) OVER(PARTITION BY A.ProductID ORDER BY A.ProductID, d.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),0) AS Total
FROM
(
SELECT ProductID, MIN(date) as DateStart, MAX(date) as DateEnd
FROM @MyTable
GROUP BY ProductID -- As I read in your comments that you need different min and max dates per product
) A
JOIN dates d ON d.Date >= A.DateStart AND d.Date <= A.DateEnd
LEFT JOIN @MyTable T ON A.ProductID = T.ProductID AND T.Date = d.Date
ORDER BY A.ProductID, d.Date
尝试以下
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp
;WITH CTE(Product,[Month],Qty)
AS
(
SELECT 'A','2018-01-01', 5 UNION ALL
SELECT 'A','2018-02-01', 3 UNION ALL
SELECT 'A','2018-05-01', 5 UNION ALL
SELECT 'B','2018-08-01', 10 UNION ALL
SELECT 'D','2018-10-01', 12
)
SELECT ct.Product,[MonthDays],ct.Qty
INTO #Temp
FROM
(
SELECT c.Product,[Month],
ISNULL(Qty,0) AS Qty
FROM CTE c
)ct
RIGHT JOIN
(
SELECT -- This code is to get month data
CONVERT(VARCHAR(10),'2018-'+ RIGHT('00'+CAST(MONTH(DATEADD(MM, s.number, CONVERT(DATETIME, 0)))AS VARCHAR),2) +'-01',120) AS [MonthDays]
FROM master.dbo.spt_values s
WHERE [type] = 'P' AND s.number BETWEEN 0 AND 11
)DT
ON dt.[MonthDays] = ct.[Month]
SELECT
MAX(Product)OVER(ORDER BY [MonthDays])AS Product,
[MonthDays],
ISNULL(Qty,0) Qty,
SUM(ISNULL(Qty,0))OVER(ORDER BY [MonthDays]) As SumQty
FROM #Temp
结果
Product MonthDays Qty SumQty
------------------------------
A 2018-01-01 5 5
A 2018-02-01 3 8
A 2018-03-01 0 8
A 2018-04-01 0 8
A 2018-05-01 5 13
A 2018-06-01 0 13
A 2018-07-01 0 13
B 2018-08-01 10 23
B 2018-09-01 0 23
D 2018-10-01 12 35
D 2018-11-01 0 35
D 2018-12-01 0 35
首先，我会划分一个月和一年，以便使用统计信息更容易。
我会给您一个示例查询，而不是基于您的表，但仍然有用。
--here i create the table that will be used as calendar
Create Table MA_MonthYears (
Month int not null ,
year int not null
PRIMARY KEY ( month, year) )
--/////////////////
-- here i'm creating a procedure to fill the ma_monthyears table
declare @month as int
declare @year as int
set @month = 1
set @year = 2015
while ( @year != 2099 )
begin
insert into MA_MonthYears(Month, year)
select @month, @year
if @month < 12
set @month=@month+1
else
set @month=1
if @month = 1
set @year = @year + 1
end
--/////////////////
--here you are the possible result you are looking for
select SUM(Ma_saledocdetail.taxableamount) as Sold, MA_MonthYears.month , MA_MonthYears.year , item
from MA_MonthYears left outer join MA_SaleDocDetail on year(MA_SaleDocDetail.DocumentDate) = MA_MonthYears.year
and Month(ma_saledocdetail.documentdate) = MA_MonthYears.Month
group by MA_SaleDocDetail.Item, MA_MonthYears.year , MA_MonthYears.month
order by MA_MonthYears.year , MA_MonthYears.month