declare @AB1_name varchar(20),@AB1_jb varchar(20)
declare acursor cursor for select * from AB1
open acursor
fetch next from acursor into @AB1_name,@AB1_jb
while @@FETCH_STATUS = 0
begin
if not exists (select * from AB2 where name=@AB1_name )
begin
insert AB2 (name) values (@AB1_name)
end
if @AB1_jb = 1
update AB2 set a1=1
if @AB1_jb = 2
update AB2 set a2=1
if @AB1_jb = 3
update AB2 set a3=1
fetch next from acursor into @AB1_name,@AB1_jb
end
close acursor
deallocate acursor
---------------------------------
应该不是最好的方法,不过可以得到你要的结果。我是SQL新手,只会这样的笨法了:-)
declare acursor cursor for select * from AB1
open acursor
fetch next from acursor into @AB1_name,@AB1_jb
while @@FETCH_STATUS = 0
begin
if not exists (select * from AB2 where name=@AB1_name )
begin
insert AB2 (name) values (@AB1_name)
end
if @AB1_jb = 1
update AB2 set a1=1
if @AB1_jb = 2
update AB2 set a2=1
if @AB1_jb = 3
update AB2 set a3=1
fetch next from acursor into @AB1_name,@AB1_jb
end
close acursor
deallocate acursor
---------------------------------
应该不是最好的方法,不过可以得到你要的结果。我是SQL新手,只会这样的笨法了:-)
select name,a1=sum(case when jb=1 then 1 else '' end),a2=sum(case when name='a' and jb=2 then 1 else '' end),
a3=sum(case when name='a' and jb=3 then 1 else '' end)
from ab1 group by name
select name,a1=sum(case jb when 1 then 1 else 0 end),a2=sum(case jb when 2 then 1 else 0 end),
a3=sum(case ib when 3 then 1 else 0 end)
from ab1 group by name
insert into ab2
select name,a1=sum(case jb when 1 then 1 else 0 end),a2=sum(case jb when 2 then 1 else 0 end),
a3=sum(case jb when 3 then 1 else 0 end)
from ab1 group by name
select name,a1=sum(case when jb=1 then 1 else '' end),a2=sum(case when name=aa.name and jb=2 then 1 else '' end),
a3=sum(case when name=aa.name and jb=3 then 1 else '' end)
from a1 aa group by name
这个一定对 看看吧