我有两个表
一个是表名：tb_gradestandard这是一个大的评分标准表
列名 数据列型 长度 允许空值
gs_id int 4
gs_standard char 20
gs_percent float 8
gs_memo char 100
另外一个表是 tb_sgradestandard 这是一个根据前面大的评分标准而生成的小的评分标准！
列名 数据列型 长度 允许空值
sgs_id int 4
sgs_standard char 20
sgs_percent float 8
gs_id int 4
sgs_memo char 100
这个表根据gs_id 与前面一个表的gs_id 相关联。现在我要控制当tb_sgradestandard的gs_id等于tb_gradestandard的gs_id 相等时在tb_sgradestandard里的sgs_percent的
总和值不能超过tb_gradestandard的gs_percent，且在tb_sgradestandard里总值不能超过１００。
其实这个问题就是tb_sgradestandard分类汇总后然后把汇总值分别与tb_gradestandard的值比较。根据相同的gs_id值来比较！因为我对SQL语句很不熟悉，所以还请各位能指点一下。
---------------------------------------------------------------
declare @条件id int,@temp1 int,@temp2 int
select @temp1=isnull(sum(sgs_percent),0) from tb_sgradestandard group by sgs_id where sgs_id=@条件id
select @temp2=sgs_percent from tb_gradestandard where sgs_id=@条件id
if (@temp1>@temp2 or @temp>100)
select '错误！'
else
select 'OK！'
---------------------------------------------------------------
想一想，少了一个ORDER BY DESC
create trigger tri_test on tb_sgradestandard
on insert,update
as
begin
declare @i int,@j int
declare @a int,@b int
set @a=(select top 1 sum(b.sgs_percent) from inserted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc)
set @b=(select top 1 sum(b.gs_percent) from inserted a,tb_gradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc)
if isnull(@a,0)>isnull(@b,0) or isnull(@a,0)>100
roalback
end
tb_gradestandard:
create trigger tri_test2 on tb_gradestandard
on update,delete
as
begin
declare @i int
declare @a int,@b int
select @i=count(*) from inserted
if @i<>0
begin
set @a=(select top 1 sum(b.sgs_percent) from inserted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc)
set @b=(select top 1 sum(b.gs_percent) from inserted a,tb_gradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc)
end
if @i=0
begin
set @a=(select top 1 sum(b.sgs_percent) from deleted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc)
set @b=(select top 1 sum(b.gs_percent) from deleted a,tb_gradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc)
end
if isnull(@a,0)>isnull(@b,0) or isnull(@a,0)>100
roalback
end