我想实现一个存储过程,大概意思如下,当然错误很多,假设变量以声明,有谁能用正确的语句重写一个
declare get_zt cursor
for select id,mc,azdd,cgmc from cddy
open get_zt
fetch get_zt into @id0,@id1,@id2,@id3
while @@fetch_status=0
begin
declare get_zt_inner cursor for
select id,sj,data,statusid from hdata
open get_zt_inner
fetch get_zt_inner into @hdata0,@hdata1,@hdata2,@hdata3
while @@fetch_status=0
select case @hdata3
case 0:
select @id4=@id4+200
case 1:
select @id5=@id5+200
case 2:
select @id6=@id6+200
case else
select @id7=@id7+200
fetch get_zt_inner into @hdata0,@hdata1,@hdata2,@hdata3
end
select @id0 as id_0 ,@id1 as id_1,@id2 as id_2,@id3 as id_3,@id4 as id_4,@id5 as id_5,
@id6 as id_6,@id7 as id_7,@id8 as id_8
fetch get_zt into @id0,@id1,@id2,@id3
end
declare get_zt cursor
for select id,mc,azdd,cgmc from cddy
open get_zt
fetch get_zt into @id0,@id1,@id2,@id3
while @@fetch_status=0
begin
declare get_zt_inner cursor for
select id,sj,data,statusid from hdata
open get_zt_inner
fetch get_zt_inner into @hdata0,@hdata1,@hdata2,@hdata3
while @@fetch_status=0
select case @hdata3
case 0:
select @id4=@id4+200
case 1:
select @id5=@id5+200
case 2:
select @id6=@id6+200
case else
select @id7=@id7+200
fetch get_zt_inner into @hdata0,@hdata1,@hdata2,@hdata3
end
select @id0 as id_0 ,@id1 as id_1,@id2 as id_2,@id3 as id_3,@id4 as id_4,@id5 as id_5,
@id6 as id_6,@id7 as id_7,@id8 as id_8
fetch get_zt into @id0,@id1,@id2,@id3
end
for select id,mc,azdd,cgmc from cddy
open get_zt
fetch get_zt into @id0,@id1,@id2,@id3
while @@fetch_status=0
begin
declare get_zt_inner cursor for
select id,sj,data,statusid from hdata
open get_zt_inner
fetch get_zt_inner into @hdata0,@hdata1,@hdata2,@hdata3
while @@fetch_status=0
select case @hdata3
when 0 then @id4=@id4+200
when 1 then @id5=@id5+200
when 2 then @id6=@id6+200
else @id7=@id7+200 end
fetch get_zt_inner into @hdata0,@hdata1,@hdata2,@hdata3
end
select @id0 as id_0 ,@id1 as id_1,@id2 as id_2,@id3 as id_3,@id4 as id_4,@id5 as id_5,
@id6 as id_6,@id7 as id_7,@id8 as id_8
fetch get_zt into @id0,@id1,@id2,@id3
end
--这样吗?
id4,id5,id6,id7 ---,id8看不到id8怎么来的
from cddy c,(
select
sum(case when statusid=0 then 200 else 0 end) as id4,
sum(case when statusid=1 then 200 else 0 end) as id5,
sum(case when statusid=2 then 200 else 0 end) as id6,
sum(case when statusid not in (0,1,2) then 200 else 0 end) as id7
from hdata
) as t
declare @cddy_id0 int,@cddy_id1 inchar(100) ,@cddy_id2 nchar(100) ,@cddy_id3 nchar(100), @cddy_id4 int,
@cddy_id5 int,@cddy_id6 int, @cddy_id7 int
declare @hdata0 nchar(100),@hdata1 nchar(100),@hdata2 nchar(100),@hdata3 nchar(100)
select @id4=0
select @id5=0
select @id6=0
select @id7=0
select @id8=0
declare get_zt cursor
for select id,mc,azdd,cgmc from cddy
open get_zt
fetch get_zt into @id0,@id1,@id2,@id3
while @@fetch_status=0
begin
declare get_zt_inner cursor for
select id,sj,data,statusid from hdata
open get_zt_inner
fetch get_zt_inner into @hdata0,@hdata1,@hdata2,@hdata3
while @@fetch_status=0
select case @hdata3
when 0 then @id4=@id4+200
when 1 then @id5=@id5+200
when 2 then @id6=@id6+200
else @id7=@id7+200 end
fetch get_zt_inner into @hdata0,@hdata1,@hdata2,@hdata3
end
select @id0 as id_0 ,@id1 as id_1,@id2 as id_2,@id3 as id_3,@id4 as id_4,@id5 as id_5,
@id6 as id_6,@id7 as id_7
fetch get_zt into @id0,@id1,@id2,@id3
end
@cddy_id5 int,@cddy_id6 int, @cddy_id7 int
declare @hdata0 nchar(100),@hdata1 nchar(100),@hdata2 nchar(100),@hdata3 nchar(100),
@id0 int,@id1 int,@id2 int,@id3 int,@id4 int,@id5 int,@id6 int,@id7 int,@id8 int
select @id4=0
select @id5=0
select @id6=0
select @id7=0
select @id8=0declare get_zt cursor
for select id,mc,azdd,cgmc from cddy
open get_zt
fetch first from get_zt into @id0,@id1,@id2,@id3
while @@fetch_status=0
begin
declare get_zt_inner cursor for
select id,sj,data,statusid from hdata
open get_zt_inner
fetch first from get_zt_inner into @hdata0,@hdata1,@hdata2,@hdata3
while @@fetch_status=0
begin
select @id4=(case @hdata3 when 0 then @id4+200 end),
@id5=(case @hdata3
when 1 then @id5+200 end),
@id6=(case @hdata3
when 2 then @id6+200 end),
@id7=(case when @hdata3>2
then @id7+200 end)
fetch next from get_zt_inner into @hdata0,@hdata1,@hdata2,@hdata3
end
select @id0 as id_0 ,@id1 as id_1,@id2 as id_2,@id3 as id_3,@id4 as id_4,@id5 as id_5,
@id6 as id_6,@id7 as id_7
fetch next from get_zt into @id0,@id1,@id2,@id3
end
CLOSE get_zt_inner
DEALLOCATE get_zt_inner
CLOSE get_zt
DEALLOCATE get_zt