大家好,麻烦帮助分析一下该存储过程。* 原意是要实现:
通过传入开始时间和结束时间,在表chldatain里查询合计该通道号(@cid)的记录数(如果表chldatain中没有盖通道号的记录,则循环到下一次运算);
然后运算比率,如果表chlrate里有该通道号的记录,则更新数据,否则作插入处理。* 问题:
我表chldatain中有通道号0、1、2、3、4、5、9的记录,下面存储过程处理后,表chlrate中多出了通道号6、7、8、10的数据,其数值跟前一个通道号数据一致!麻烦大家帮忙看看逻辑哪里错误了,急用、一时半会没想明白,谢谢!!!
--CREATE PROCEDURE sp_hw_chlrate --090918_01version
--ALTER PROCEDURE sp_hw_chlrate --090918_02version@i_stime varchar(8), --输入参数:开始时间,char型,(112)yyyymmdd格式
@i_etime varchar(8) --输入参数:结束时间,char型,(112)yyyymmdd格式ASDeclare
@cid int, --定义通道号,用于循环赋值以查询记录表、插入或更新比率表
@totalnum int, --所有通道号的记录总数
@bnum int, --单个通道号的记录总数
@bnum_dec decimal, --转换格式
@brate decimal(5,2) --通道的记录比率 Set @totalnum = (select sum(cast(insid as int(4))) from chldatain)
Set @cid=0While (@cid<=10)
BEGIN
----操作通道号
IF EXISTS (SELECT 1 FROM chldatain WHERE channelid=@cid)
Set @bnum = (select count(*) from chldatain
where channelid=@cid
and convert(char(8),instime,112)>@i_stime
and convert(char(8),instime,112)<@i_etime
)
Set @bnum_dec = (select cast(@bnum as decimal(18,4)))
Set @brate = (select cast(((@bnum_dec / @totalnum)*100) as decimal(5,2)))
IF EXISTS (SELECT 1 FROM chlrate WHERE chlid = @cid)
UPDATE chlrate set sdatetime=@i_stime,
edatetime=@i_etime,
chlcount=@bnum,
brate = @brate
where chlid = @cid
ELSE
INSERT into chlrate(sdatetime,edatetime,chlid,chlcount,brate) values(@i_stime,@i_etime,@cid,@bnum,@brate)Set @cid=@cid+1
Set @bnum=0END
GO
通过传入开始时间和结束时间,在表chldatain里查询合计该通道号(@cid)的记录数(如果表chldatain中没有盖通道号的记录,则循环到下一次运算);
然后运算比率,如果表chlrate里有该通道号的记录,则更新数据,否则作插入处理。* 问题:
我表chldatain中有通道号0、1、2、3、4、5、9的记录,下面存储过程处理后,表chlrate中多出了通道号6、7、8、10的数据,其数值跟前一个通道号数据一致!麻烦大家帮忙看看逻辑哪里错误了,急用、一时半会没想明白,谢谢!!!
--CREATE PROCEDURE sp_hw_chlrate --090918_01version
--ALTER PROCEDURE sp_hw_chlrate --090918_02version@i_stime varchar(8), --输入参数:开始时间,char型,(112)yyyymmdd格式
@i_etime varchar(8) --输入参数:结束时间,char型,(112)yyyymmdd格式ASDeclare
@cid int, --定义通道号,用于循环赋值以查询记录表、插入或更新比率表
@totalnum int, --所有通道号的记录总数
@bnum int, --单个通道号的记录总数
@bnum_dec decimal, --转换格式
@brate decimal(5,2) --通道的记录比率 Set @totalnum = (select sum(cast(insid as int(4))) from chldatain)
Set @cid=0While (@cid<=10)
BEGIN
----操作通道号
IF EXISTS (SELECT 1 FROM chldatain WHERE channelid=@cid)
Set @bnum = (select count(*) from chldatain
where channelid=@cid
and convert(char(8),instime,112)>@i_stime
and convert(char(8),instime,112)<@i_etime
)
Set @bnum_dec = (select cast(@bnum as decimal(18,4)))
Set @brate = (select cast(((@bnum_dec / @totalnum)*100) as decimal(5,2)))
IF EXISTS (SELECT 1 FROM chlrate WHERE chlid = @cid)
UPDATE chlrate set sdatetime=@i_stime,
edatetime=@i_etime,
chlcount=@bnum,
brate = @brate
where chlid = @cid
ELSE
INSERT into chlrate(sdatetime,edatetime,chlid,chlcount,brate) values(@i_stime,@i_etime,@cid,@bnum,@brate)Set @cid=@cid+1
Set @bnum=0END
GO
BEGIN
----操作通道号
IF not EXISTS (SELECT 1 FROM chldatain WHERE channelid=@cid)
continue
Set @bnum = (select count(*) from chldatain
where channelid=@cid
and convert(char(8),instime,112)>@i_stime
and convert(char(8),instime,112)<@i_etime
)
Set @bnum_dec = (select cast(@bnum as decimal(18,4)))
Set @brate = (select cast(((@bnum_dec / @totalnum)*100) as decimal(5,2)))
IF EXISTS (SELECT 1 FROM chlrate WHERE chlid = @cid)
UPDATE chlrate set sdatetime=@i_stime,
edatetime=@i_etime,
chlcount=@bnum,
brate = @brate
where chlid = @cid
ELSE
INSERT into chlrate(sdatetime,edatetime,chlid,chlcount,brate) values(@i_stime,@i_etime,@cid,@bnum,@brate)Set @cid=@cid+1
Set @bnum=0END
----操作通道号
IF EXISTS (SELECT 1 FROM chldatain WHERE channelid=@cid)
begin
Set @bnum = (select count(*) from chldatain
where channelid=@cid
and convert(char(8),instime,112)>@i_stime
and convert(char(8),instime,112)<@i_etime
)
Set @bnum_dec = (select cast(@bnum as decimal(18,4)))
Set @brate = (select cast(((@bnum_dec / @totalnum)*100) as decimal(5,2)))
IF EXISTS (SELECT 1 FROM chlrate WHERE chlid = @cid)
UPDATE chlrate set sdatetime=@i_stime,
edatetime=@i_etime,
chlcount=@bnum,
brate = @brate
where chlid = @cid
ELSE
INSERT into chlrate(sdatetime,edatetime,chlid,chlcount,brate) values(@i_stime,@i_etime,@cid,@bnum,@brate)
end
BEGIN
----操作通道号
IF EXISTS (SELECT 1 FROM chldatain WHERE channelid=@cid)
BEGIN
Set @bnum = (select count(*) from chldatain
where channelid=@cid
and convert(char(8),instime,112)>@i_stime
and convert(char(8),instime,112)<@i_etime
)
Set @bnum_dec = (select cast(@bnum as decimal(18,4)))
Set @brate = (select cast(((@bnum_dec / @totalnum)*100) as decimal(5,2)))
IF EXISTS (SELECT 1 FROM chlrate WHERE chlid = @cid)
UPDATE chlrate set sdatetime=@i_stime,
edatetime=@i_etime,
chlcount=@bnum,
brate = @brate
where chlid = @cid
ELSE
INSERT into chlrate(sdatetime,edatetime,chlid,chlcount,brate) values(@i_stime,@i_etime,@cid,@bnum,@brate)
END
Set @cid=@cid+1
Set @bnum=0END
While (@cid <=10)
BEGIN
----操作通道号
IF not EXISTS (SELECT 1 FROM chldatain WHERE channelid=@cid)
begin
Set @cid=@cid+1
continue
end
Set @bnum = (select count(*) from chldatain
where channelid=@cid
and convert(char(8),instime,112)>@i_stime
and convert(char(8),instime,112) <@i_etime
)
Set @bnum_dec = (select cast(@bnum as decimal(18,4)))
Set @brate = (select cast(((@bnum_dec / @totalnum)*100) as decimal(5,2)))
IF EXISTS (SELECT 1 FROM chlrate WHERE chlid = @cid)
UPDATE chlrate set sdatetime=@i_stime,
edatetime=@i_etime,
chlcount=@bnum,
brate = @brate
where chlid = @cid
ELSE
INSERT into chlrate(sdatetime,edatetime,chlid,chlcount,brate) values(@i_stime,@i_etime,@cid,@bnum,@brate) Set @cid=@cid+1
Set @bnum=0 END
ELSE
if exists(select 1 FROM chldatain WHERE channelid = @cid) ---else 之后判断该@cid是不是在 chldatain 存在。不存在,不会插入
insert ...
就是没想到反过来想想,基础差呀。。
下加begin Set @cid=@cid+1之前加end也可以
重新试了试,ok!!!good~
begin
.
.
.
end用begin、end括起来