表TB1,实例如下:
入库1 入库2 入库3 入库4 入库5 …………入库30 入库31 合计
100 350 150 120 100 300 30 100+350+150+120+……+300+30
…………………………
…………………………
12 132 12 33 44 ………… 34 11 12+132+12+33+44……+34+11
范例如上就是已经有的数据是字段: 入库1,入库2,………………一直到字段入库31 但是字段"合计"的值是需要每一行的入库1+入库2+入库3+……+入库30+入库31的和
请问SQL语句该如何写
入库1 入库2 入库3 入库4 入库5 …………入库30 入库31 合计
100 350 150 120 100 300 30 100+350+150+120+……+300+30
…………………………
…………………………
12 132 12 33 44 ………… 34 11 12+132+12+33+44……+34+11
范例如上就是已经有的数据是字段: 入库1,入库2,………………一直到字段入库31 但是字段"合计"的值是需要每一行的入库1+入库2+入库3+……+入库30+入库31的和
请问SQL语句该如何写
sum(入库1) as 合计
from
(
select 入库1 from tb
union all
select 入库2 from tb
union all
....
select 入库30 from tb)t
declare @tb table([入库1] int,[入库2] int,[入库3] int,[入库4] int,[入库5] int,[入库30] int,[入库31] int,[合计] int)
insert @tb
select 100,350,150,120,100,300,30,null union all
select 12,132,12,33,44,34,11,null
--
UPDATE A SET
合计=(SELECT x.value('sum(/x/*)','int')
FROM (SELECT x=(SELECT A.* FOR XML PATH('x'),TYPE)) AS T)
FROM @tb AS ASELECT * FROM @tb
declare @tb table([入库1] int,[入库2] int,[入库3] int,[入库4] int,[入库5] int,[入库30] int,[入库31] int,[合计] int)
insert @tb
select 100,350,150,120,100,300,30,null union all
select 12,132,12,33,44,34,11,null
--
UPDATE A SET
合计=(SELECT A.* FOR XML PATH('x'),TYPE).value('sum(/x/*)','int')
FROM @tb AS ASELECT * FROM @tb
这样?
set @sql = ''
set @sql = 'select '
set @i = 1
while @i < 32
begin
set @sql=@sql+'入库'+cast(@i as varchar(5))+','
set @sql2 = @sql2 + '+' @sql2
set @i = @i + 1
end
set @sql = @sql + ',('+@sql2+')' from TB1
exec (@sql)
未测试
set @sql = ''
set @sql2 = ''
set @sql = 'select '
set @i = 1
while @i < 32
begin
set @sql=@sql+'入库'+cast(@i as varchar(5))+','
set @sql2 = @sql2 + '入库'+cast(@i as varchar(10))+'+'
set @i = @i + 1
end
set @sql2 = left(@sql2,len(@sql2) - 1)
set @sql = @sql + '('+@sql2+') from TB1'
--print @sql
exec (@sql)