sqlserver2000 简单的存储过程

为了让一个多值表中的数据，通过合理的处理来自动生成一个想要的简易表，并且是把原数据表中的数据合拢后按规定格式放在一行中。这样就可以在查询时，按行进行查询。整个存储过程使用了两个循环，而后考虑各个特定数据对应的行，通过行标来更新相应行数据。

declare @i int, @j int,@k int,@m int,@a int
set @i=1 set @j=(select count(train_code) from zzcz.dbo.temp_sto_price)
while @i<=@j     //第一级循环
begin
create table zzcz.dbo.temp_sto_price1(train_code char(8),xh int identity(1,1),station_name char(10),station_no char(2))
insert into zzcz.dbo.temp_sto_price1(train_code,station_name,station_no) select distinct(train_code),station_name,station_no from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price where xh=@i)  order by station_no  asc

insert into zzcz.dbo.t_lspj(cc,xh,zm) select train_code,convert(int,station_no),station_name from zzcz.dbo.temp_sto_price1
set @k=convert(int,(select station_no from zzcz.dbo.temp_sto_price1 where xh=1)) set @m=convert( int ,(select station_no from zzcz.dbo.temp_sto_price1 where xh=(select count(station_name) from zzcz.dbo.temp_sto_price1)))
while @k<=@m    //第二级循环
begin
set @a=(select xh from zzcz.dbo.temp_sto_price1 where station_no=@k)

if((select substring(train_code,1,1) from zzcz.dbo.temp_sto_price where xh=@i)='T')
begin
//开始更新数据

update zzcz.dbo.t_lspj set yz=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='10' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k

update zzcz.dbo.t_lspj set rz=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='20' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k
end
else
begin

update zzcz.dbo.t_lspj set yz=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='O0' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k

update zzcz.dbo.t_lspj set rz=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='M0' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k
end

update zzcz.dbo.t_lspj set yws=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='31' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k

update zzcz.dbo.t_lspj set ywz=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='32' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k

update zzcz.dbo.t_lspj set ywx=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='33' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k

update zzcz.dbo.t_lspj set rws=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='41' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k

update zzcz.dbo.t_lspj set rwx=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='43' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k

update zzcz.dbo.t_lspj set sw=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='90' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k

update zzcz.dbo.t_lspj set td=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='P0' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k

update zzcz.dbo.t_lspj set gg=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='Q0' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k

update zzcz.dbo.t_lspj set ydbz=convert(varchar(10),convert(int, (select ticket_price from zzcz.dbo.sto_price where train_code=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and station_name=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and seat_type_code='S0' and station_no=(select station_no from zzcz.dbo.temp_sto_price1 where xh=@a)))) where cc=(select train_code from zzcz.dbo.temp_sto_price1 where xh=@a) and zm=(select station_name from zzcz.dbo.temp_sto_price1 where xh=@a) and xh=@k

set @k=@k+1  //里层循环增量
end

drop table zzcz.dbo.temp_sto_price1

set @i = @i+1 //外层循环增量
end

//从这一个存储过程中可以看出数据处理的复杂性和原理的简易性。如果要处理一组很复杂的数据，首先应该从原理上去找突破点，找到原理性的东西后，按编程的思想把存储过程写下来，然后翻译成sql语句。再慢慢调试，最后得到想要的结果。调试时可以采用单数据调试，便于过程的运行，这样成功后，再全面调试，找到出错的位置，然后在这一位置处分析数据，找处特殊数据，来进行修改。以满足全部数据的处理。