两个表,tableA和tableB,A表已知/num字段会不断变化,查询B表的时候获取每个chlid的比率( num / sum(nums) )
tableA
id channelid num
1 0 31
2 1 21
3 5 14
4 8 5tableB
id chlid brate
1 0
2 1
3 5
4 8 这里涉及到循环查A表行数据计算插入到B表中,绕来绕去我都晕了:(求教,谢谢!!!
tableA
id channelid num
1 0 31
2 1 21
3 5 14
4 8 5tableB
id chlid brate
1 0
2 1
3 5
4 8 这里涉及到循环查A表行数据计算插入到B表中,绕来绕去我都晕了:(求教,谢谢!!!
from tableA a,tableB b
where a.id = b.id
from (select id, channelid,brate=num*1.0/(select sum(num) from tb) from tb) a
where tb.id=a.id and a.channelid=b.chlid
if object_id('a') is not null
drop table a
create table a
(
id int,
channelid int,
num int
)insert a
select 1,0,31 union all
select 2,1,21 union all
select 3,5,14 union all
select 4,8,5
if object_id('b') is not null
drop table b
create table b
(
id int,
chlid int,
brate float
)
insert b
select 1,0,0 union all
select 2,1,0 union all
select 3,5,0 union all
select 4,8,0 update b set brate = a.num * 1.00 / (select sum(num) from a)
from a,b where a.id = b.idselect * from a
select * from b /**
id channelid num
----------- ----------- -----------
1 0 31
2 1 21
3 5 14
4 8 5(所影响的行数为 4 行)id chlid brate
----------- ----------- -----------------------------------------------------
1 0 0.43661971830980001
2 1 0.29577464788730001
3 5 0.19718309859149999
4 8 7.0422535211199996E-2(所影响的行数为 4 行)**/
update b set brate = a.num * 1.00 / (select sum(num) from a)
from a inner join b on a.id = b.id
from (select id, channelid,brate=num*1.0/(select sum(num) from tb) from tb) a
where tb.id=a.id and a.channelid=b.chlid--可以写个触发器,在A表数据变化时更新B表
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-05 11:29:54
---------------------------------
--> 生成测试数据表:tableAIf not object_id('[tableA]') is null
Drop table [tableA]
Go
Create table [tableA]([id] int,[channelid] int,[num] int)
Insert tableA
Select 1,0,31 union all
Select 2,1,21 union all
Select 3,5,14 union all
Select 4,8,5
Go
--Select * from tableA--> 生成测试数据表:tableBIf not object_id('[tableB]') is null
Drop table [tableB]
Go
Create table [tableB]([id] int,[chlid] int,[brate] nvarchar(10))
Insert tableB
Select 1,0,null union all
Select 2,1,null union all
Select 3,5,null union all
Select 4,8,null
Go
--Select * from tableB-->SQL查询如下:
update [tableB] set
brate=a.brate
from (
select id, channelid,brate=ltrim(cast(num*1.0/(select sum(num) from [tableA])*100 as dec(18,2)))+'%'
from [tableA]
) as a
where [tableB].id=a.id and a.channelid=[tableB].chlidselect * from tableB
/*
id chlid brate
----------- ----------- ----------
1 0 43.66%
2 1 29.58%
3 5 19.72%
4 8 7.04%(4 行受影响)
*/
Insert TA
select 1,0,31 union all
select 2,1,21 union all
select 3,5,14 union all
select 4,8,5
create table [TB]([id] int,[chlid] int,[brate] nvarchar(10))
Insert TB
select 1,0,null union all
select 2,1,null union all
select 3,5,null union all
select 4,8,nullcreate proc UpdateTB
as
begin
declare @Sum int--这样效率略高
select @Sum=sum([num])
from TA update b
set brate=ltrim(cast(a.num*100.0/@Sum as dec(18,2)))+'%'
from [TA] a,[TB] b
where b.id=a.id and a.channelid=b.chlid
endexec UpdateTBselect * from TBid chlid brate
----------- ----------- ----------
1 0 43.66%
2 1 29.58%
3 5 19.72%
4 8 7.04%
用最笨的法子写了个存储过程。Declare定义参数,在查询分析器里执行语句可以。但是exec sp_hw_chlrptA
却不行,提示
(所影响的行数为 7 行)
(所影响的行数为 7 行)
服务器: 消息 201,级别 16,状态 4,过程 sp_hw_chlrptA,行 0
过程 'sp_hw_chlrptA' 需要参数 '@chlid',但未提供该参数。CREATE PROCEDURE sp_hw_chlrptA
@chlid int,
@cid int,
@totalnum int,
@bnum int,
@bnum_dec decimal,
@brate decimal(5,2)
AS
BEGIN Set @totalnum = (select sum(cast(num as int(4))) from channel)IF EXISTS (SELECT 1 FROM channel WHERE channelid=0)
Set @chlid=(select 0)
Set @cid=(select 0)
Set @bnum = (SELECT cast(num as int) from channel where channelid =@cid)
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 chlcalc WHERE chlid = @chlid)
UPDATE chlcalc set brate = @brate where chlid = @cid
ELSE
insert into chlcalc (chlid,brate) values (@chlid,@brate)IF EXISTS (SELECT 1 FROM channel WHERE channelid=1)
Set @chlid=(select 1)
Set @cid=(select 1)
Set @bnum = (SELECT cast(num as int) from channel where channelid =@cid)
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 chlcalc WHERE chlid = @chlid)
UPDATE chlcalc set brate = @brate where chlid = @cid
ELSE
insert into chlcalc (chlid,brate) values (@chlid,@brate)IF EXISTS (SELECT 1 FROM channel WHERE channelid=2)
Set @chlid=(select 2)
Set @cid=(select 2)
Set @bnum = (SELECT cast(num as int) from channel where channelid =@cid)
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 chlcalc WHERE chlid = @chlid)
UPDATE chlcalc set brate = @brate where chlid = @cid
ELSE
insert into chlcalc (chlid,brate) values (@chlid,@brate)IF EXISTS (SELECT 1 FROM channel WHERE channelid=3)
Set @chlid=(select 3)
Set @cid=(select 3)
Set @bnum = (SELECT cast(num as int) from channel where channelid =@cid)
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 chlcalc WHERE chlid = @chlid)
UPDATE chlcalc set brate = @brate where chlid = @cid
ELSE
insert into chlcalc (chlid,brate) values (@chlid,@brate)IF EXISTS (SELECT 1 FROM channel WHERE channelid=4)
Set @chlid=(select 4)
Set @cid=(select 4)
Set @bnum = (SELECT cast(num as int) from channel where channelid =@cid)
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 chlcalc WHERE chlid = @chlid)
UPDATE chlcalc set brate = @brate where chlid = @cid
ELSE
insert into chlcalc (chlid,brate) values (@chlid,@brate)IF EXISTS (SELECT 1 FROM channel WHERE channelid=5)
Set @chlid=(select 5)
Set @cid=(select 5)
Set @bnum = (SELECT cast(num as int) from channel where channelid =@cid)
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 chlcalc WHERE chlid = @chlid)
UPDATE chlcalc set brate = @brate where chlid = @cid
ELSE
insert into chlcalc (chlid,brate) values (@chlid,@brate)IF EXISTS (SELECT 1 FROM channel WHERE channelid=9)
Set @chlid=(select 9)
Set @cid=(select 9)
Set @bnum = (SELECT cast(num as int) from channel where channelid =@cid)
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 chlcalc WHERE chlid = @chlid)
UPDATE chlcalc set brate = @brate where chlid = @cid
ELSE
insert into chlcalc (chlid,brate) values (@chlid,@brate)
END
GO
thx
12楼里的定义方式是输入参数,exec时需要指定参数值,所以……结!!!thx all!