有一题目，求解，谢谢
题目：给每个科目里拖后腿低于平均分的人成绩加上3分的友情赞助分
CREATE TABLE [dbo].[Stud_Grade](
[Strd_ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Course_id] [int] NULL,
[Grade] [decimal](18, 2) NULL,
PRIMARY KEY CLUSTERED
(
[Strd_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

insert Stud_Grade(Strd_ID,Name,Course_id,Grade) values (1,'张三',1,95.50)
insert Stud_Grade(Strd_ID,Name,Course_id,Grade) values (2,'李四',1,69.12)
insert Stud_Grade(Strd_ID,Name,Course_id,Grade) values (3,'王五',2,64.50)
insert Stud_Grade(Strd_ID,Name,Course_id,Grade) values (4,'马六',3,66.60)
insert Stud_Grade(Strd_ID,Name,Course_id,Grade) values (5,'田七',2,30.00)
insert Stud_Grade(Strd_ID,Name,Course_id,Grade) values (6,'杜八',3,45.00)
insert Stud_Grade(Strd_ID,Name,Course_id,Grade) values (7,'黄三',2,99.00)
insert Stud_Grade(Strd_ID,Name,Course_id,Grade) values (8,'张艺',3,25.00)
go


[解决办法]
UPDATE Stud_Grade SET grade=grade+3 WHERE grade<
(
SELECT SUM(grade)/COUNT(strd_id) FROM Stud_Grade
)
[解决办法]

UPDATE Stud_Grade SET grade=grade+3 WHERE grade<
(
SELECT AVG(grade) FROM Stud_Grade
)

UPDATE Stud_Grade SET grade=grade+3 
WHERE grade<( SELECT AVG(a.grade) FROM Stud_Grade a WHERE a.Course_id=Course_id)

select garde=grade+3 from(
select a.Strd_ID ,a.Course_id,a.grade , b.avgsc from Stud_Grade  as a left join
(select  Course_id  ,avg([grade]) as avgsc  from Stud_Grade group by Course_id ) as b
on a.Course_id=b.Course_id 
where a.Grade<b.avgsc) as d

CREATE TABLE [dbo].[Stud_Grade](
 [Strd_ID] [int] NOT NULL,
 [Name] [nvarchar](50) NULL,
 [Course_id] [int] NULL,
 [Grade] [decimal](18, 2) NULL,
 PRIMARY KEY CLUSTERED 
 (
 [Strd_ID] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY] 
 

 GO

 insert Stud_Grade(Strd_ID,Name,Course_id,Grade) values (1,'张三',1,95.50),
 (2,'李四',1,69.12),
 (3,'王五',2,64.50),
(4,'马六',3,66.60),
(5,'田七',2,30.00),
(6,'杜八',3,45.00),
(7,'黄三',2,99.00),
(8,'张艺',3,25.00)
select * from Stud_Grade
go 
;with sel as(
select course_id,avg_grade=AVG(grade) from Stud_Grade group by Course_id
)
update sg set grade=grade+3 from Stud_Grade sg,sel
where sg.Course_id=sel.Course_id and grade<sel.avg_grade
select * from Stud_Grade