日期:2014-05-17 浏览次数:20700 次
insert into B总分平均分表(id ,name ,zongfen ,pingjunfen)
select id,name,sum(xueke),avg(xueke)
from A成绩表
group by id,name
insert into B总分平均分表(id ,name ,zongfen ,pingjunfen)
select id,name,sum(chengji),avg(chengji)
from A成绩表
group by id,name
INSERT INTO B表
(
id,
name,
zongfen,
pingjunfen
)
SELECT
id,
name,
SUM(chengji),
CAST(AVG(1.0*chengji) AS DECIMAL(10,1))
FROM A表
GROUP BY id, name
CREATE TRIGGER [dbo].[tr_stat]
ON [dbo].[A]
AFTER insert
AS
BEGIN
update table5 set zongfen=a.zongfen,pingjunfen=a.pingjunfen
from
(
select t.id,t.name,sum(chengji) as zongfen ,avg(chengji) as pingjunfen from
(
select inserted.* from inserted
inner join B on inserted.id=B.id
union
select A.* from A
inner join B on A.id=B.id
)t group by t.id,t.name
) a inner join B on B.id=a.id