34,587
社区成员
发帖
与我相关
我的任务
分享
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(xueke),avg(xueke)
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
insert into B
select t.id,t.name,t.chengji,t.chengji from inserted t
left join B a on t.id=a.id
where a.id is null
END
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