如何在MS SQL Server存储过程中声明数组？
如何在MS SQL Server存储过程中声明数组？
提问于 2009-11-14 07:48:12
我需要声明12个十进制变量，对应于每个月的年份，使用游标对这些变量求和，然后更新一些销售信息。
我不知道sql server是否有此语法。
Declare MonthsSale(1 to 12) as decimal(18,2)这段代码运行正常。好了！
CREATE PROCEDURE [dbo].[proc_test]
AS
BEGIN
--SET NOCOUNT ON;
DECLARE @monthsales TABLE ( monthnr int, amount decimal(18,2) )
-- PUT YOUR OWN CODE HERE
-- THIS IS TEST CODE
-- 1 REPRESENTS JANUARY, ...
INSERT @monthsales (monthnr, amount) VALUES (1, 100)
INSERT @monthsales (monthnr, amount) VALUES (1, 100)
INSERT @monthsales (monthnr, amount) VALUES (2, 200)
INSERT @monthsales (monthnr, amount) VALUES (3, 300)
INSERT @monthsales (monthnr, amount) VALUES (4, 400)
INSERT @monthsales (monthnr, amount) VALUES (5, 500)
INSERT @monthsales (monthnr, amount) VALUES (6, 600)
INSERT @monthsales (monthnr, amount) VALUES (7, 700)
INSERT @monthsales (monthnr, amount) VALUES (8, 800)
INSERT @monthsales (monthnr, amount) VALUES (9, 900)
INSERT @monthsales (monthnr, amount) VALUES (10, 1000)
INSERT @monthsales (monthnr, amount) VALUES (11, 1100)
INSERT @monthsales (monthnr, amount) VALUES (12, 1200)
SELECT monthnr, SUM(amount) AS SUM_MONTH_1 FROM @monthsales WHERE monthnr = 1 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_2 FROM @monthsales WHERE monthnr = 2 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_3 FROM @monthsales WHERE monthnr = 3 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_4 FROM @monthsales WHERE monthnr = 4 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_5 FROM @monthsales WHERE monthnr = 5 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_6 FROM @monthsales WHERE monthnr = 6 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_7 FROM @monthsales WHERE monthnr = 7 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_8 FROM @monthsales WHERE monthnr = 8 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_9 FROM @monthsales WHERE monthnr = 9 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_10 FROM @monthsales WHERE monthnr = 10 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_11 FROM @monthsales WHERE monthnr = 11 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_12 FROM @monthsales WHERE monthnr = 12 GROUP BY monthnr
-- END TEST CODE
END回答 5
Stack Overflow用户
回答已采纳
发布于 2009-11-14 07:53:47
您可以声明一个表变量(声明一个类型为table的变量)：
declare @MonthsSale table(monthnr int)
insert into @MonthsSale (monthnr) values (1)
insert into @MonthsSale (monthnr) values (2)
....您可以根据需要添加额外的列：
declare @MonthsSale table(monthnr int, totalsales tinyint)您可以像更新任何其他表一样更新表变量：
update m
set m.TotalSales = sum(s.SalesValue)
from @MonthsSale m
left join Sales s on month(s.SalesDt) = m.MonthNrStack Overflow用户
发布于 2009-11-14 07:57:16
为什么不使用表变量和聚合SUM运算符，而不使用游标？SQL擅长面向集合的操作。99.87%的时间，你发现自己在使用游标，有一个面向集合的替代方案，它更有效：
declare @MonthsSale table
(
MonthNumber int,
MonthName varchar(9),
MonthSale decimal(18,2)
)
insert into @MonthsSale
select
1, 'January', 100.00
union select
2, 'February', 200.00
union select
3, 'March', 300.00
union select
4, 'April', 400.00
union select
5, 'May', 500.00
union select
6, 'June', 600.00
union select
7, 'July', 700.00
union select
8, 'August', 800.00
union select
9, 'September', 900.00
union select
10, 'October', 1000.00
union select
11, 'November', 1100.00
union select
12, 'December', 1200.00
select * from @MonthsSale
select SUM(MonthSale) as [TotalSales] from @MonthsSaleStack Overflow用户
发布于 2009-11-14 07:51:55
据我所知，T-SQL不支持数组。
你们的表格结构是什么？你可以设计一个查询来实现这一点：
select
month,
sum(sales)
from sales_table
group by month
order by month页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1732613
复制相关文章
相似问题
腾讯云开发者
