一个SQL不能实现,作存储过程实现吧!
DECLARE
RD STU1%ROWTYPE;
AAA NUMBER;
CURSOR My_Cur IS
SELECT DISTINCT no,score
FROM stu1
WHERE score > 0;
BEGINOPEN My_Cur;
LOOP
FETCH My_Cur INTO RD;
IF My_Cur%NOTFOUND THEN
EXIT;
END IF;
SELECT COUNT(*) INTO AAA FROM STU2 WHERE NO = RD.NO;
IF AAA > 0 THEN
UPDATE STU2
SET SCORE = RD.SCORE
WHERE NO = RE.NO;
ELSE
INSERT INTO STU2("NO","SCORE") VALUES(RD.NO,RD.SCORE);
END IF;
END;
DECLARE
RD STU1%ROWTYPE;
AAA NUMBER;
CURSOR My_Cur IS
SELECT DISTINCT no,score
FROM stu1
WHERE score > 0;
BEGINOPEN My_Cur;
LOOP
FETCH My_Cur INTO RD;
IF My_Cur%NOTFOUND THEN
EXIT;
END IF;
SELECT COUNT(*) INTO AAA FROM STU2 WHERE NO = RD.NO;
IF AAA > 0 THEN
UPDATE STU2
SET SCORE = RD.SCORE
WHERE NO = RE.NO;
ELSE
INSERT INTO STU2("NO","SCORE") VALUES(RD.NO,RD.SCORE);
END IF;
END;
a.no=b.no and a.score>0)
a.no=b.no and a.score>0)
where a.no in(select no from stu2)