|

楼主 |
发表于 2020-4-17 17:18:32
|
显示全部楼层
CREATE TABLE SC_AVG_SCORE
(
CNO VARCHAR(255) PRIMARY KEY(CNO),
AVG_SCORE FLOAT
)
INSERT INTO SC_AVG_SCORE(CNO,AVG_SCORE)
SELECT CNO,AVG(SCORE)
FROM SC
GROUP BY CNO;
SELECT * FROM SC;
UPDATE SC SET SC.SCORE = SC_AVG_SCORE.AVG_SCORE FROM SC,SC_AVG_SCORE
WHERE SC_AVG_SCORE.CNO = SC.CNO AND SC.SCORE < SC_AVG_SCORE.AVG_SCORE;
SELECT * FROM SC;
或者
SELECT * FROM SC;
UPDATE SC SET SC.SCORE = SC_AVG_SCORE1.AVG_SCORE
FROM SC,( SELECT CNO,AVG(SCORE) AS AVG_SCORE
FROM SC
GROUP BY CNO
) AS SC_AVG_SCORE1
WHERE SC_AVG_SCORE1.CNO = SC.CNO AND SC.SCORE < SC_AVG_SCORE1.AVG_SCORE;
SELECT * FROM SC;
|
|