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

Question

我需要声明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

- RicardoBalda

Answer 1

您可以声明一个表变量（声明类型为表的变量）：

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.MonthNr

- Andomar

---

我想使用内存解决方案，因为我希望保护我的固态硬盘免受过多的写入访问。这可行吗？数组中的数据量将很少，但会非常频繁地创建。 - Paul

Answer 2

你是否有使用表变量和聚合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 @MonthsSale

- Paul Smith

---

据说在 MSSQL2012 中，现在可以使用以下格式进行插入： VALUES(1, 'January', 100.00),(2, 'February', 200.00) - 来源：http://blog.sqlauthority.com/2012/10/27/sql-server-storing-variable-values-in-temporary-array-or-temporary-list/ - andrewb

这个功能完全逃过了我的注意，显然在 SQL 2008 中也可以使用。 - Paul Smith

Answer 3

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

你的表结构是什么？您可能可以设计一个查询来完成这个任务：

select
month,
sum(sales)
from sales_table
group by month
order by month

- Patrick Burleson

---

仅作为旁注，我想指出语法 T[n].v 比 (select v from T where T.i = n) 更简洁。实际上，它要简洁得多。我很想看到 T-SQL 加入它。 - debater

Answer 4

伟大的问题和想法，但在SQL中，您需要这样做：

对于数据类型datetime，可以像这样进行操作：

declare @BeginDate    datetime = '1/1/2016',
        @EndDate      datetime = '12/1/2016'
create table #months (dates datetime)
declare @var datetime = @BeginDate
   while @var < dateadd(MONTH, +1, @EndDate)
   Begin
          insert into #months Values(@var)
          set @var = Dateadd(MONTH, +1, @var)
   end

如果你只需要得到数字，那就这样做 -

create table #numbas (digit int)
declare @var int = 1        --your starting digit
    while @var <= 12        --your ending digit
    begin
        insert into #numbas Values(@var)
        set @var = @var +1
    end

- Dane Thomas

Answer 5

你可以使用VALUES关键字声明一个数组。你的示例可以用以下简洁的形式表示：

SELECT * FROM (VALUES 
    (1,100), 
    (2,200), 
    (3,300), 
    (4,400), 
    (5,500), 
    (6,600), 
    (7,700), 
    (8,800), 
    (9,900), 
    (10,1000), 
    (11,1100), 
    (12,1200)
) MonthSale (monthnr, Amount)

随意将其插入表中，或为您的目的设置WHERE或GROUP条件。

- Y-Mi Wong