主表 SALE_MAIN SNO NUM 001 002 从表 SALE_SUB sno num_sub 001 10 001 20 001 13 002 3 002 5 当从表添加、删除、修改时候,要触发主表,保证NUM的总数等于从表的num_sub之和 对我有用[0] 丢个板砖[0] 引用 举报 管理 TOP 回复次数:8 sgtzzc (四方城) 等 级: #1楼 得分:0回复于:2010-01-12 23:12:33SQL codecreate trigger tri_sale_sub on sale_sub for insert,update,delete as if not exists(select 1 from inserted i,sale_main m where i.sno=m.sno) begin insert sale_main select sno,sum(num_sub) from inserted group by sno end else begin update a set a.num=a.num+b.num from sale_main a, (select sno,sum(num) as num from (select sno,num_sub as num from inserted union all select sno,-num_sub as num from deleted )a group by sno )b where a.sno=b.sno end 是这个意思吗,相同的就累加,不同的就在表2中新增
这样写哪错了帮忙看看declare @tb table ([姓名] varchar,[总数] varchar(50)) set @tb='Table1' insert into @tb select '张三',10 insert into @tb select '张三',30 insert into @tb select '张三',10 insert into @tb select '李四',20 insert into @tb select '李四',35 insert into @tb select '李四',40 insert into @tb select '王五',10 insert into @tb select '王五',20 select * 当日分数=0 into # from @tb order by convert 姓名 desc declare @i int,@xm1 varchar(10),@xm2 varchar(10),@j int select @xm1 = '',@xm2='',@j = 0,@i=0 update # set 当日分数=累计分数 - @j,@xm2 = @xm1,@j = case when @xm2 = 姓名 then @i else 0 end,@i = 累计分数,@xm1 = 姓名 select * from # drop table # go
declare @tb table ([姓名] varchar(50),[总数] varchar(50)) --set @tb='Table1' insert into @tb select '张三',10 insert into @tb select '张三',30 insert into @tb select '张三',10 insert into @tb select '李四',20 insert into @tb select '李四',35 insert into @tb select '李四',40 insert into @tb select '王五',10 insert into @tb select '王五',20 select *,当日分数=0 from @tb order by [姓名] desc就修改了一点,后面的不理解"累计分数"那里来的
SNO NUM
001
002 从表 SALE_SUB
sno num_sub
001 10
001 20
001 13
002 3
002 5 当从表添加、删除、修改时候,要触发主表,保证NUM的总数等于从表的num_sub之和 对我有用[0] 丢个板砖[0] 引用 举报 管理 TOP 回复次数:8 sgtzzc (四方城) 等 级:
#1楼 得分:0回复于:2010-01-12 23:12:33SQL codecreate trigger tri_sale_sub
on sale_sub
for insert,update,delete
as
if not exists(select 1 from inserted i,sale_main m where i.sno=m.sno)
begin
insert sale_main
select sno,sum(num_sub) from inserted group by sno
end
else
begin
update a
set a.num=a.num+b.num
from
sale_main a,
(select sno,sum(num) as num
from
(select sno,num_sub as num from inserted
union all
select sno,-num_sub as num from deleted
)a
group by sno
)b
where a.sno=b.sno
end 是这个意思吗,相同的就累加,不同的就在表2中新增
insert into @tb select '张三',10
insert into @tb select '张三',30
insert into @tb select '张三',10
insert into @tb select '李四',20
insert into @tb select '李四',35
insert into @tb select '李四',40
insert into @tb select '王五',10
insert into @tb select '王五',20
select * 当日分数=0 into # from @tb order by convert 姓名 desc
declare @i int,@xm1 varchar(10),@xm2 varchar(10),@j int
select @xm1 = '',@xm2='',@j = 0,@i=0
update # set 当日分数=累计分数 - @j,@xm2 = @xm1,@j = case when @xm2 = 姓名 then @i else 0 end,@i = 累计分数,@xm1 = 姓名
select * from #
drop table #
go
declare @tb table ([姓名] varchar(50),[总数] varchar(50))
--set @tb='Table1'
insert into @tb select '张三',10
insert into @tb select '张三',30
insert into @tb select '张三',10
insert into @tb select '李四',20
insert into @tb select '李四',35
insert into @tb select '李四',40
insert into @tb select '王五',10
insert into @tb select '王五',20
select *,当日分数=0 from @tb order by [姓名] desc就修改了一点,后面的不理解"累计分数"那里来的