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语句。
解决方案 »
- 通过count函数求课程数和人数,急急急啊
- 用dblink有哪些坏处?100分
- 紧急求救!Oracle不能连接到远程服务器!
- 独占和共享启动方式中所谓的并行选件是在什么地方设置哟?
- update该怎么用?
- 请问developer suite是developer2000吗?
- oracle8.17能否在win98下装服务吗? 在线等待
- Oracle中有没有类似SQL Server中Space(10)这样的函数?
- oracle为什么连不上了,快快救命
- ORACLE权限问题
- oracle数据库导出导入后出现很多小红叉,比方说存储过程,进入之后空格然后编译无问题
- 為什麼別名不能用在where 中???????
是要这么写的
你好
你可以讲的清楚点吗?
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