declare @i int
declare @str_sql nvarchar(500)
select @i=1
while @i<=12
begin
select @str_sql='update tab2 set month'+convert(nvarchar(2),@i)+'=(select sum(num) from tab1 where month<='+convert(nvarchar(2),@i)+')
exec(@str_sql)
select @i=@i+1
continue
end 思路:在循环里先构造SQL语句,然后用执行动态SQL的方法更新tab2
declare @str_sql nvarchar(500)
select @i=1
while @i<=12
begin
select @str_sql='update tab2 set month'+convert(nvarchar(2),@i)+'=(select sum(num) from tab1 where month<='+convert(nvarchar(2),@i)+')
exec(@str_sql)
select @i=@i+1
continue
end 思路:在循环里先构造SQL语句,然后用执行动态SQL的方法更新tab2
tab1
id proj year month num
1 1 2004 1 15
2 1 2004 2 20
3 2 2004 1 15
4 1 2004 3 30
5 2 2004 4 20
得到的tab2应该是
tab2
id proj year month1 month2 month3 month4 …… month12
1 1 2004 15 35 65
2 2 2004 15 35
怎样由tab1得到tab2
declare @s varchar(8000),@i int
select @s='',@i=max(month) from tab1
while @i>0
select @s=',month'+cast(@i as varchar)+'=sum(case month when '
+cast(@i as varchar)+' then num else 0 end)'+@s
,@i=@i-1
exec('select id,proj,year'+@s+' from tab1 group by id,proj,year')
create table tab1(id int,proj int,year int,month int,num int)
insert tab1 select 1,1,2004,1,15
union all select 2,1,2004,2,20
union all select 3,2,2004,1,15
union all select 4,1,2004,3,30
union all select 5,2,2004,4,20
go--查询处理
declare @s varchar(8000),@i int
select @s='',@i=max(month) from tab1
while @i>0
select @s=',month'+cast(@i as varchar)+'=sum(case month when '
+cast(@i as varchar)+' then num else 0 end)'+@s
,@i=@i-1
exec('select id,proj,year'+@s+' from tab1 group by id,proj,year')
go--删除测试
drop table tab1/*--测试结果
id proj year month1 month2 month3 month4
----------- ----------- ----------- ----------- ----------- ----------- ----1 1 2004 15 0 0 0
2 1 2004 0 20 0 0
3 2 2004 15 0 0 0
4 1 2004 0 0 30 0
5 2 2004 0 0 0 20
--*/
比方说
id name
1 adfh
2 123972
3 weoru
4 12347
用SQL怎样得到比记录集中只有
id name
4 12347
要比较简捷的
create table a
(
id int identity not null,
name varchar(20)
)insert into a(name)
select 'adfh'
union select '12'
union select 'adfasdfadf'select * from aselect id ,name from a
where a.id = (select max(id) as id from a)