TABLE A: STUDENT、GRADE
TABLE B: STUDENT、TYPE
TABLE C: STUDENT、SCORE
现在要将C表中所有学生的SCORE 存入A表中,当TYPE为 a时 GRADE=SCORE*0.5,TYPE为b时,GRADE=SCORE*0.4,TYPE为c时,GRADE=SCORE*0.3
这个SQL语句要怎么写啊 用IF 和CASE分别怎么来写?
TABLE B: STUDENT、TYPE
TABLE C: STUDENT、SCORE
现在要将C表中所有学生的SCORE 存入A表中,当TYPE为 a时 GRADE=SCORE*0.5,TYPE为b时,GRADE=SCORE*0.4,TYPE为c时,GRADE=SCORE*0.3
这个SQL语句要怎么写啊 用IF 和CASE分别怎么来写?
select c.student,
(case when type='a' then c.score*0.5
when type='b' then c.score*0.4
when type='c' then c.score*0.3
else then score end) grade
from c,b where c.student=b.student
select STUDENT, decode(B.TYPE, 'a', C.SCORE*0.5, 'b', C.SCORE*0.4, 'c', C.SCORE*0.3, 0)
from B, C
where B.STUDENT = C.STUDENT用decode更好
select c.student,decode(b.type,'a',c.score*0.5,'b',score*0.4,'c',score*0.3) grade
from B,C where b.student=c.student;
-----使用insert ALL
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
Connected as XXXXX
SQL> drop table tableA;Table droppedSQL> drop table tableB;Table droppedSQL> drop table tableC;Table droppedSQL>
SQL> create table tableA
2 (
3 student varchar2(100),
4 grade number
5 )
6 /Table createdSQL>
SQL> create table tableB
2 (
3 student varchar2(100),
4 type varchar2(2)
5 )
6 /Table createdSQL>
SQL> create table tableC
2 (
3 student varchar2(100),
4 score number
5 )
6 /Table createdSQL>
SQL> insert into tableB values('aa','a');1 row insertedSQL> insert into tableC values('aa',100);1 row insertedSQL> insert into tableB values('bb','b');1 row insertedSQL> insert into tableC values('bb',100);1 row insertedSQL> insert into tableB values('cc','c');1 row insertedSQL> insert into tableC values('cc',100);1 row insertedSQL> insert into tableB values('dd','c');1 row insertedSQL> insert into tableC values('dd',100);1 row insertedSQL> commit;Commit completeSQL>
SQL> INSERT ALL
2 WHEN type='a' THEN INTO tableA values(student,score*0.5)
3 WHEN type='b' THEN INTO tableA VALUES (student, score*0.5)
4 ELSE INTO tableA VALUES(student, score*0.3)
5 select c.student,c.score,b.type from TABLEC c,TABLEB b where c.student=b.student;4 rows insertedSQL> commit;Commit completeSQL> select * from tableA;STUDENT GRADE
-------------------------------------------------------------------------------- ----------
aa 50
bb 50
cc 30
dd 30SQL> drop table tableA;Table droppedSQL> drop table tableB;Table droppedSQL> drop table tableC;Table droppedSQL>
好比说:Cursor cs is select type from B;open cs;
loop
fetch cs into v_type;下面我要对TYPE进行判断时,IF v_type='a'....此处是不是要对B表中的所有数据进行遍历,找出type为a的进行IF下面的操作 ?
不过还解决不了 再来这里问
select type from B;
V_TYPE CHAR(1);OPEN CS;
LOOP
FETCH CS INTO V_TYPE;
IF V_TYPE='a' THEN INSERT INTO A(STUDENT,GRADE) SELECT B.STUDENT,C.SCORE*0.5 FROM A,B,C WHERE A.STUDENT=B.STUDENT AND A.STUDENT=C.STUDENT;
ELSE
INSERT INTO A(STUDENT,GRADE)........
END IF;
COMMIT;
END LOOP;
CLOSE CS;
这个怎么不执行IF下的操作啊真的找不明白了啊。。
select a,b,c
from xxx
where xxx
)
loop
process(x.a);
process(x.b);
end loop;
桌子上掉
SQL >删除表表b。
桌子上掉
SQL >删除表tableC。
桌子上掉
SQL >
merge into A using
(select c.STUDENT,decode(b.type,'a',c.SCORE*0.5,'b',c.SCORE*0.4,'c',c.SCORE*0.3) nscore from b,c where b.STUDENT=c.STUDENT) d on(a.STUDENT=d.STUDENT)
when matched then
update set a.GRADE=d.nscore
when not matched then
insert(a.STUDENT,a,GRADE) values(d.STUDENT,d.nscore)--insert into a
select c.STUDENT,
decode(b.type,'a',c.SCORE*0.5,'b',c.SCORE*0.4,'c',c.SCORE*0.3) nscore
from b,c
where b.STUDENT=c.STUDENT