oracle行转列
select i.sid,i.sname,i.birthday,i.schooltime,i.sphone,c.classname,a.assnname,sum(decode(subject,'语文',s.score,0)) as chin,
sum(decode(subject,'数学',s.score,0)) as math,
sum(decode(subject,'英语',s.score,0)) as eng from student_info i
left join student_expand e on i.sid = e.sid left join student_class c on
c.classid = e.classid left join student_assn a on a.assnid = e.assnid left join student_score s on s.sid = i.sid
group by i.sid,i.sname,i.birthday,i.schooltime,i.sphone,c.classname,a.assnname;
行转列:
decode(subject,'数学',s.score,0) 用法:如果subject会等于数学 ,则最后值等于 s.score 否则值等于: 0
相关文档：
1. 创建普通用户
>create user username identified by username;
>grant create tablespace, resource ,connect to username;
2. 创建表空间和用户
2.1 创建临时表空间
>create temporary tablespace DB_TEMP
tempfile '/opt/oracle/oradata/mydb/t ......
当本机安装了ORACLE数据库后，再安装运行OC4J的话，环境变量“ORACLE_HOME”与ORACLE数据库有冲突，可以按照如下方法解决此问题：
方法一
第一步：在环境变量中新建“OC4J_HOME”，指定到你的OC4J安装根目录；
第二步：打开OC4J安装的目录，即../OC4J/bin/，里面有一个oc4j.cmd，右键单击该文件，� ......
60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS> insert into table3 values(gao,1111.11);
SQLWKS> insert into table3 values(gao,1111.11);
SQLWKS> insert into table3 values(zhu ......
1. 查看Oracle创建过哪些用户
>select username from all_users;
2. 查看Oracle创建过哪些表空间，表空间的名字和大小
>select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
wher ......
CREATE OR REPLACE TYPE ty_str_array IS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN ty_str_split
IS
j INT := 0;
i INT := 1;
len INT := 0;
&nbs ......
