摘要： ORACLE--Connect By、Level、Start With的使用(Hierarchical query-层次查询)Syntax 1CONNECT BY[NOCYCLE] <condition>START WITH<condition>Syntax 2START WITH<condition>CONNECT BY[NOCYCLE] <condition>参考网址:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:489772591421 http:// 阅读全文

摘要： 有些时候我们希望得到指定数据中的前n列，示例如下：得到每个部门薪水最高的三个雇员：先创建示例表createtableempasselect*fromscott.emp;altertableempaddconstraintemp_pkprimarykey(empno);createtabledeptasselect*fromscott.dept;altertabledeptaddconstraintdept_pkprimarykey(deptno);先看一下row_number() /rank()/dense_rank()三个函数之间的区别selectemp.deptno,emp.sal,emp 阅读全文

摘要： 第一种：select custid,sum(case when YEAR(orderdate)=2002 then qty end) as [2002],sum(case when YEAR(orderdate)=2003 then qty end) as [2003],sum(case when YEAR(orderdate)=2004 then qty end) as [2004]from ordersgroup by custid;第二种：select *from (select custid,YEAR(orderdate) as years,qty from orders) as or 阅读全文