有一题目，求解，谢谢
题目：给每个科目里拖后腿低于平均分的人成绩加上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
