CREATE TABLE ZLB_STUDENT (ID INT, SUBJECT INT, SCORE NUMBER(3, 1));
INSERT INTO ZLB_STUDENT VALUES (1, 1, 95);
INSERT INTO ZLB_STUDENT VALUES (1, 2, 96);
INSERT INTO ZLB_STUDENT VALUES (1, 3, 97);
INSERT INTO ZLB_STUDENT VALUES (2, 1, 98);
INSERT INTO ZLB_STUDENT VALUES (2, 2, 96);
INSERT INTO ZLB_STUDENT VALUES (2, 3, 94);
SELECT * FROM ZLB_STUDENT;
CREATE TABLE ZLB_SUBJECT (ID INT, NAME VARCHAR2(50));
INSERT INTO ZLB_SUBJECT VALUES (1, 'ENGLISH');
INSERT INTO ZLB_SUBJECT VALUES (2, 'MATH');
INSERT INTO ZLB_SUBJECT VALUES (3, 'CHINESE');
SELECT * FROM ZLB_SUBJECT;
CREATE OR REPLACE VIEW ZLB_STUDENT_SUBJECT AS
SELECT ID, SUM(ENGLISH) ENGLISH, SUM(MATH) MATH, SUM(CHINESE) CHINESE
FROM
(
SELECT A.ID ID,
CASE WHEN B.NAME='ENGLISH' THEN NVL(A.SCORE, 0) ELSE 0 END ENGLISH,
CASE WHEN B.NAME='MATH' THEN NVL(A.SCORE, 0) ELSE 0 END MATH,
CASE WHEN B.NAME='CHINESE' THEN NVL(A.SCORE, 0) ELSE 0 END CHINESE
FROM ZLB_STUDENT A, ZLB_SUBJECT B
WHERE A.SUBJECT = B.ID
)
GROUP BY ID;
SELECT * FROM ZLB_STUDENT_SUBJECT;
有没有办法在生成视图ZLB_STUDENT_SUBJECT时,SQL语句自动从表ZLB_SUBJECT中查询得到列名,
而不是人工用CASE书写?
这是小弟现在最想知道的SQL语句。
INSERT INTO ZLB_STUDENT VALUES (1, 1, 95);
INSERT INTO ZLB_STUDENT VALUES (1, 2, 96);
INSERT INTO ZLB_STUDENT VALUES (1, 3, 97);
INSERT INTO ZLB_STUDENT VALUES (2, 1, 98);
INSERT INTO ZLB_STUDENT VALUES (2, 2, 96);
INSERT INTO ZLB_STUDENT VALUES (2, 3, 94);
SELECT * FROM ZLB_STUDENT;
CREATE TABLE ZLB_SUBJECT (ID INT, NAME VARCHAR2(50));
INSERT INTO ZLB_SUBJECT VALUES (1, 'ENGLISH');
INSERT INTO ZLB_SUBJECT VALUES (2, 'MATH');
INSERT INTO ZLB_SUBJECT VALUES (3, 'CHINESE');
SELECT * FROM ZLB_SUBJECT;
CREATE OR REPLACE VIEW ZLB_STUDENT_SUBJECT AS
SELECT ID, SUM(ENGLISH) ENGLISH, SUM(MATH) MATH, SUM(CHINESE) CHINESE
FROM
(
SELECT A.ID ID,
CASE WHEN B.NAME='ENGLISH' THEN NVL(A.SCORE, 0) ELSE 0 END ENGLISH,
CASE WHEN B.NAME='MATH' THEN NVL(A.SCORE, 0) ELSE 0 END MATH,
CASE WHEN B.NAME='CHINESE' THEN NVL(A.SCORE, 0) ELSE 0 END CHINESE
FROM ZLB_STUDENT A, ZLB_SUBJECT B
WHERE A.SUBJECT = B.ID
)
GROUP BY ID;
SELECT * FROM ZLB_STUDENT_SUBJECT;
有没有办法在生成视图ZLB_STUDENT_SUBJECT时,SQL语句自动从表ZLB_SUBJECT中查询得到列名,
而不是人工用CASE书写?
这是小弟现在最想知道的SQL语句。
是要这么写的
你好
你可以讲的清楚点吗?
o(∩_∩)o
create or replace procedure p_view(nview_name varchar2)
authid current_user
as
str varchar2(1000);
str1 varchar2(1000);
begin
for i in(select distinct name from ZLB_SUBJECT) loop
str1:=str1||','||'sum(decode(b.name,'''||i.name||''',nvl(a.score,0))) '||i.name;
end loop;
str:='create or replace view '||nview_name||' as select a.id '||str1
||' from ZLB_STUDENT A, ZLB_SUBJECT B where A.SUBJECT = B.ID group by a.id';
execute immediate str;
end;SQL> create or replace procedure p_view(nview_name varchar2)
2 authid current_user
3 as
4 str varchar2(1000);
5 str1 varchar2(1000);
6 begin
7 for i in(select distinct name from ZLB_SUBJECT) loop
8 str1:=str1||','||'sum(decode(b.name,'''||i.name||''',nvl(a.score,0))) '||i.name;
9 end loop;
10 str:='create or replace view '||nview_name||' as select a.id '||str1
11 ||' from ZLB_STUDENT A, ZLB_SUBJECT B where A.SUBJECT = B.ID group by a.id';
12 execute immediate str;
13 end;
14 /
Procedure created
SQL> exec p_view('ZLB_STUDENT_SUBJECT')
PL/SQL procedure successfully completed
SQL> select * from ZLB_STUDENT_SUBJECT
2 /
ID MATH CHINESE ENGLISH
--------------------------------------- ---------- ---------- ----------
1 96 97 95
2 96 94 98