我一直在互聯網上尋找這個解決方案，但沒有一個能正常作業。我決定創建變數，然后將其容納在用戶定義的函式中：
CREATE FUNCTION Top10CustomerByCategoryInYear
(
@Category varchar(MAX),
@StartYear int,
@EndYear int
)
RETURNS @CustomerTop10TableByCategoryInYears TABLE
(
[Customer Name] VARCHAR(MAX),
[Category] VARCHAR(MAX),
Sales int,
Year int
)
AS
BEGIN
INSERT INTO @CustomerTop10TableByCategoryInYears
SELECT
DISTINCT TOP 10 WITH TIES
[Customer Name],
[Category],
SUM(Sales) OVER (PARTITION BY [Customer Name]) as Sales,
YearOfOrderDate as [Year]
FROM [Project4].[dbo].[SuperStore]
WHERE [Category] = @Category AND YearOfOrderDate BETWEEN @StartYear AND @EndYear
ORDER BY Sales DESC
RETURN;
END
GO
它作業正常，因為例如我能夠回傳 2011 年和 2012 年家具類別中的前 10 名客戶
SELECT *
FROM Top10CustomerByCategoryInYear('Furniture',2011,2011)
SELECT *
FROM Top10CustomerByCategoryInYear('Furniture',2012,2012)
然后，我想在一張表中包含所有記錄（2011-2014）。我決定使用 CTE，然后按類別加入這些表：
WITH Furniture2011 AS (
SELECT DISTINCT *
FROM Top10CustomerByCategoryInYear('Furniture',2011,2011)
), Furniture2012 AS (
SELECT DISTINCT *
FROM Top10CustomerByCategoryInYear('Furniture',2012,2012)
), Furniture2013 AS (
SELECT DISTINCT *
FROM Top10CustomerByCategoryInYear('Furniture',2013,2013)
), Furniture2014 AS (
SELECT DISTINCT *
FROM Top10CustomerByCategoryInYear('Furniture',2014,2014)
)
SELECT DISTINCT *
FROM Furniture2011 F1
JOIN Furniture2012 F2
ON F1.Category = F2.Category
JOIN Furniture2013 F3
ON F1.Category = F3.Category
JOIN Furniture2014 F4
ON F1.Category = F4.Category
不幸的是，有很多重復：
[
我從我的頭腦和互聯網上嘗試了很多解決方案，但不幸的是這些都沒有奏效。我想知道是否可以使用 UDF/Variables、TEMP TABLE 和最后的 CTE 組合這些表。你能告訴我是否可能嗎？如果是這樣，您是否愿意支持我修改 SQL 中的代碼以獲得所需的結果而沒有任何重復？
如果你能給我建議，我將不勝感激。
uj5u.com熱心網友回復：
您可以使用行內表函式來計算開始和結束年份。行內比多陳述句更快。
從一個虛擬的年份表開始，然后CROSS APPLY是每年的前 10 個結果。
CREATE FUNCTION Top10CustomerByCategoryInYear
(
@Category varchar(MAX),
@StartYear int,
@EndYear int
)
RETURNS TABLE
AS RETURN
WITH L0 AS (
SELECT @StartYear - 1 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Year
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1)) AS D(c)
),
Years AS (
SELECT * FROM L0 WHERE Year <= @EndYear
)
SELECT
s.[Customer Name],
@Category AS [Category],
s.Sales,
y.Year
FROM Years y
CROSS APPLY (
SELECT TOP 10 WITH TIES
[Customer Name],
SUM(Sales) AS Sales
FROM [dbo].[SuperStore] s
WHERE [Category] = @Category
AND y.Year = s.YearOfOrderDate
GROUP BY [Customer Name]
ORDER BY Sales DESC
) s;
GO
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/316315.html
標籤：sql sql-server