SQL面试练习题二(常用函数)

继续使用前一篇文章中使用的表和数据做练习。

对日期函数的使用：

Count(*) of orders last week

SELECT COUNT(*)
FROM order_list
WHERE order_date < TRUNC(SYSDATE,'DAY')
AND order_date >= TRUNC(SYSDATE,'DAY')-7

Count(*) of orders by month

SELECT TO_CHAR(order_date,'yyyy/mm'),COUNT(*)
FROM order_list
GROUP BY TO_CHAR(order_date,'yyyy/mm');

或

select extract(year from order_date) ||'-' || extract(month from order_date), count(*)
from order_list
group by extract(year from order_date) ||'-' || extract(month from order_date);

找出到现在为止入职满一年的雇员

SELECT *
FROM employee
WHERE months_between(SYSDATE,start_date) > 12;

SELECT *
FROM employee
where start_date < sysdate-365

使用DECODE函数：

decode函数是一种类似于if-then-else逻辑的一个函数。如下例中，当TRUNC(order_date,'YEAR')与TO_DATE(’2000-01-01‘)相同时，则返回‘2000-01-01’，如果没有匹配上则继续向后匹配。如果没有匹配上，则返回NULL。

SELECT DECODE(TRUNC(order_date,'YEAR'),
              TO_DATE('2000-01-01','YYYY-mm-DD'),'2000-01-01',
              TO_DATE('2001-01-01','YYYY-mm-DD'),'2001-01-01',
              TO_DATE('2002-01-01','YYYY-mm-DD'),'2002-01-01',
              TO_DATE('2003-01-01','YYYY-mm-DD'),'2003-01-01',
              TO_DATE('2004-01-01','YYYY-mm-DD'),'2004-01-01',
              TO_DATE('2005-01-01','YYYY-mm-DD'),'2005-01-01') new_date
FROM order_list;

使用PIVOT函数：

PIVOT是Oracle 11g引入的新函数，用于行列时的汇总统计。注意pivot必须使用汇总函数，所以如果仅仅是做行列转换，则不能使用pivot。

将Order_list表中将购买客户变成列显示：

原数据：

Order_id, Customer_id, order_date
O1, C1, 01-Jan-2000
O2, C2, 01-Jan-2002
O3, C3, 01-Apr-2002
O4, C4, 01-Apr-2003
O5, C4, 01-Jan-2006
O6,C1, 01-May-2006

行列转换后：

C1,C2,C3,C4
2    1    1   2

SELECT *
FROM
  ( SELECT
      ORDER_ID,
      CUSTOMER_ID
    FROM order_list t
  )
pivot ( COUNT(ORDER_ID) FOR CUSTOMER_ID IN ('C1','C2','C3','C4') ) ;

显示每个部门中不同职责的人数：

部分原数据：

     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM    DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ----------- ---------
      7902 FORD       ANALYST         7566 03_12月_1981       3000            20
      7934 MILLER     CLERK           7782 23_1月 _1982       1300            10

转换后的数据：

    DEPTNO 'SALESMAN'    'CLERK'  'MANAGER'  'ANALYST'
---------- ---------- ---------- ---------- ----------
        10          0          1          1          0
        20          0          2          1          2
        30          4          1          1          0

SELECT *
FROM
  (
    SELECT ename,job,deptno 
    FROM emp
  )
  PIVOT (COUNT(ename) FOR job IN ('SALESMAN','CLERK','MANAGER','ANALYST'))
ORDER BY deptno;

RANK()和DENSE_RANK()

rank即是汇总函数，也可以是分析函数，二者语法不同。这里按汇总函数来使用。例如显示工资1500在公司所有雇员中的排名是多少。注意rank的参数只能是常量，不能是变量。

select rank(1500) within group (order by sal)
from employee

select dep_id,rank(15000) within group (order by salary desc)
from employee
group by dep_id //分部门统计

行列转换

简单的行列转换。例如将下例中职员的职位名称变为列名。

部分原数据：

     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM    DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ----------- ---------
      7902 FORD       ANALYST         7566 03_12月_1981       3000            20
      7934 MILLER     CLERK           7782 23_1月 _1982       1300            10


转换结果：

     EMPNO CLERK      SALESMAN   MANAGER  
---------- ---------- ---------- ----------
      7499            ALLEN                
      7369 SMITH                           
      7521            WARD                 
      7566                       JONES     
      7654            MARTIN               
      7698                       BLAKE     
      7782                       CLARK     
      7788                                 
      7839                                 
      7844            TURNER               
      7876 ADAMS

 

select empno,case job when 'CLERK' then ename end as clerk,
case job when 'SALESMAN' then ename end salesman,
case job when 'MANAGER' then ename end manager
from emp ;

另一种行列转换则需要用函数来实现。

c1 c2
--- -----------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不

这类需求通常用函数来解决。