SQL Server Management Studio中的循环
我得到了一个717个SKU的列表，我必须通过这些SKU将年销售额和销售量的总价值相加。我开发了一个代码来按年搜索SKU，并收到总金额。我想知道是否有一种方法，我可以循环输入更多的SKU的数量，这样我就不必逐个通过SKU。
我熟悉循环语句，但并不擅长执行它们。我想知道在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的格式。
回答 3
Stack Overflow用户
发布于 2019-05-02 01:39:51
不要从循环的角度考虑问题。从集合的角度思考。
这使用了一个用于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_Stack Overflow用户
发布于 2019-05-02 01:38:49
如果需要传递多个SKU，可以使用表变量：
DECLARE @SDate date
SET @SDate = '01/01/2018'
DECLARE @EDate date
SET @EDate = '12/31/2018'
DECLARE @Skus table (SKU varchar(20);
INSERT INTO @SKUs
VALUES('SN1580'),
('SN0350');
SELECT SIL.[No_] AS SKU,
SUM(SIL.Amount) AS Amount,
SUM(SIL.Quanity) AS Quantity
FROM dbo.[Threshold Enterprises$Sales Invoice Line] SIL --I recommeond against special characters (including white space) in object names
JOIN @SKUs S ON SIL.[No_] = S.SKU
WHERE SIL.[Shipment Date] BETWEEN @SDate AND @EDate
GROUP BY SIL.[No_];如果这是使用此参数化查询的应用程序，则可能需要创建用户定义的表类型，然后使用该表类型：
CREATE TYPE dbo.SKUs AS TABLE (SKU varchar(20));微软的文档涵盖了如何在Table-Valued Parameters中使用它们。
边注:如果您的列[Shipment Date]同时具有日期值和时间值(并且时间可以是00:00:00以外的值)，我建议您不要使用BETWEEN。这是因为最后一天午夜之后的任何时间都不会在这两个日期之间。例如，2018-04-30 00:00:00.003不在2018-04-01和2018-04-30之间。
因此，如果您有日期和时间值，我建议使用此子句：
WHERE SIL.[Shipment Date] >= @SDate
AND SIL.[Shipment Date] < DATEADD(DAY, 1, @EDate)Stack Overflow用户
发布于 2019-05-02 01:28:42
您应该能够使用in语句在一个查询中完成所有操作，在IN语句中使用逗号分隔的列表。根据SQL技术的不同，您的语法可能会略有不同。
SELECT No_ AS SKU, SUM(Amount) AS Amount, SUM(Quantity) AS Quantity
FROM dbo.[Threshold Enterprises$Sales Invoice Line]
WHERE ([Shipment Date] BETWEEN @SDate AND @EDate)
AND (No_ IN
('SN1580', 'SN0350')
)
GROUP BY No_;或者，如果您的SKU在表中，您可以将in更改为：
AND (No_ IN
(Select SKU from yourTableHere)
)https://stackoverflow.com/questions/55940074
复制相似问题
腾讯云开发者
