我得到了一份包含 717 个 SKU 的清单,我必须将这些清单加起来,将年销售额和售出单位的总值相加。我开发了一个代码来按年份搜索 SKU 并接收总金额。我想知道是否有一种方法可以让我循环输入更多 SKU,这样我就不必逐个逐个查看 SKU。
我熟悉 Loop 语句,但不擅长执行它们。想知道是否有办法在 Microsoft SQL Server Management Studio 2017 中执行此操作。
我试过声明和重复代码,但效率低下。
DECLARE @SDate date
SET @SDate = '01/01/2018'
DECLARE @EDate date
SET @EDate = '12/31/2018'
DECLARE @Sku varchar(20)
SET @Sku = 'SN1580'
SELECT SUM(Amount) AS EXPR1
FROM dbo.[Threshold Enterprises$Sales Invoice Line]
WHERE ([Shipment Date] BETWEEN @SDate AND @EDate) AND (No_ = N'SN1580')
SELECT SUM(Quantity) AS EXPR1
FROM dbo.[Threshold Enterprises$Sales Invoice Line]
WHERE ([Shipment Date] BETWEEN @SDate AND @EDate) AND (No_ = N'SN1580')
SELECT SUM(Amount) AS EXPR1
FROM dbo.[Threshold Enterprises$Sales Invoice Line]
WHERE ([Shipment Date] BETWEEN @SDate AND @EDate) AND (No_ = N'SN0350')
SELECT SUM(Quantity) AS EXPR1
FROM dbo.[Threshold Enterprises$Sales Invoice Line]
WHERE ([Shipment Date] BETWEEN @SDate AND @EDate) AND (No_ = N'SN0350')
期待结果能给我一些类似的东西
SN1234
Amount 1000
Sum 200
SN3456
Amount 2000
Sum 100
或者,是否有办法以易于导出到 excel 的格式获取结果。
不要从循环的角度考虑。从集合的角度考虑。
这将表变量用于 sku 列表,但您可以使用 CTE、实际表、VALUE
列表;实际上,任何能让 SQL Server 将您的所有搜索词作为数据集而不是作为单独输入处理的东西。
DECLARE @SDate date = '01/01/2018'
DECLARE @EDate date = '12/31/2018'
DECLARE @Sku TABLE
(
Sku varchar(20)
);
INSERT @Sku (Sku)
VALUES (N'SN1580'),
(N'SN0350'); --<--Add your list here. Maybe use Excel to make the wrappers.
SELECT
l.No_
,SUM(l.Amount) AS Amount
,SUM(l.Quantity) AS Quantity
FROM
dbo.[Threshold Enterprises$Sales Invoice Line] AS l
JOIN
@Sku AS s
ON s.Sku = l.No_
WHERE
l.[Shipment Date] BETWEEN @SDate AND @EDate
GROUP BY
l.No_
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句