首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >如何在MS SQL Server存储过程中声明数组?

如何在MS SQL Server存储过程中声明数组?
EN

Stack Overflow用户
提问于 2009-11-14 07:48:12
回答 5查看 488.3K关注 0票数 87

我需要声明12个十进制变量,对应于每个月的年份,使用游标对这些变量求和,然后更新一些销售信息。

我不知道sql server是否有此语法。

代码语言:javascript
复制
 Declare MonthsSale(1 to 12) as decimal(18,2)

这段代码运行正常。好了!

代码语言:javascript
复制
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
EN

回答 5

Stack Overflow用户

回答已采纳

发布于 2009-11-14 07:53:47

您可以声明一个表变量(声明一个类型为table的变量):

代码语言:javascript
复制
declare @MonthsSale table(monthnr int)
insert into @MonthsSale (monthnr) values (1)
insert into @MonthsSale (monthnr) values (2)
....

您可以根据需要添加额外的列:

代码语言:javascript
复制
declare @MonthsSale table(monthnr int, totalsales tinyint)

您可以像更新任何其他表一样更新表变量:

代码语言:javascript
复制
update m
set m.TotalSales = sum(s.SalesValue)
from @MonthsSale m
left join Sales s on month(s.SalesDt) = m.MonthNr
票数 147
EN

Stack Overflow用户

发布于 2009-11-14 07:57:16

为什么不使用表变量和聚合SUM运算符,而不使用游标?SQL擅长面向集合的操作。99.87%的时间,你发现自己在使用游标,有一个面向集合的替代方案,它更有效:

代码语言:javascript
复制
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 @MonthsSale
票数 27
EN

Stack Overflow用户

发布于 2009-11-14 07:51:55

据我所知,T-SQL不支持数组。

你们的表格结构是什么?你可以设计一个查询来实现这一点:

代码语言:javascript
复制
select
month,
sum(sales)
from sales_table
group by month
order by month
票数 8
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/1732613

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档