phporacle提交commit技巧_第34期Java面试题commit运用行列转换order by和group by

group by 分组查询、having 只能用于group by子句、浸染于组内，having条件子句可以直接跟函数表达式。
利用group by 子句的查询语句须要利用聚合函数。

oracle的commit便是DML语句提交数据（这里是开释锁不是锁表），在未提交前你前面的操作更新的都是内存，没有更新到物理文件中。

实行commit从用户角度讲便是更新到物理文件了，事实上commit时还没有写date file，而是记录了redo log file，要从内存写到data物理文件，须要触发检讨点，由DBWR这个后台进程来写，这里内容有点多的，如果不穷究的话你就理解成commit即为从内存更新到物理文件。

行转列

1）利用decode函数

select name,

sum(decode(course, '语文', score, 0)) as 语文,

sum(decode(course, '数学', score, 0)) as 数学,

sum(decode(course, '英语', score, 0)) as 英语

from GRADE group by name;

2）利用case when语句

select name,

sum(case course when '语文' then score else 0 end) as 语文,

sum(case course when '数学' then score else 0 end) as 数学,

sum(case course when '英语' then score else 0 end) as 英语

from GRADE group by name;

select name,

sum(case when course='语文' then score else 0 end) as 语文,

sum(case when course='数学' then score else 0 end) as 数学,

sum(case when course='英语' then score else 0 end) as 英语

from GRADE group by name;

列转行

select name, '语文' as course, cn_score as score from SC_GRADE

union all

select name, '数学' as course, math_score as score from SC_GRADE

union all

select name, '英语' as course, en_score as score from SC_GRADE

order by name;

oracle