9.原表:
courseid  coursename score
1        Java        70
2        oracle      90
3        xml         40
4        jsp         30
5        servlet     80

为了便于阅读, 查询此表后的结果显式如下( 及格分数为60):

courseid coursename score mark
1        Java       70    pass
2        oracle     90    pass
3        xml        40    fail
4        jsp        30    fail
5        servlet    80    pass

分析:
新增一列,并由Case When语句进行判断

SELECT S.*,
       CASE WHEN SCORE>=60 THEN 'pass' ELSE 'fail' END AS MARK
 FROM SC_JUN S    

10.不用组函数求EMP表中薪水最高的员工信息。

分析: 聚合函数+ 分析函数 条件筛选等于1的所有信息。

SELECT *
    FROM (SELECT E.*, RANK() OVER(ORDER BY SAL DESC) AS RAK FROM EMP E) T
   WHERE T.RAK = 1;

11.求每个部门工资高于部门平均工资的员工数量占整个部门人数的百分比

分析:用分析函数新增一列平均工资,再有Case When条件判断1或者0,并将合适的人数进行累加,最后和部门的总人数相比,得出百分比。

方法一(较为简约):

SELECT DEPTNO,
       COUNT(EMPNO),
       SUM(CASE WHEN SAL>AVG_SAL THEN 1 ELSE 0 END),
       CONCAT(ROUND(SUM(CASE WHEN SAL>AVG_SAL THEN 1 ELSE 0 END)/COUNT(EMPNO)*100,2),'%') AS RATE
  FROM (SELECT E.*,
               AVG(SAL) OVER (PARTITION BY DEPTNO) AS AVG_SAL
          FROM EMP E) T
 GROUP BY DEPTNO;

方法二(逻辑较清晰):

SELECT A.DEPTNO, TO_CHAR(ROUND((人数/总人数)*100,2),'99D99')|| '%' AS 百分比 FROM 
  (SELECT DEPTNO,COUNT(*) AS 总人数 FROM EMP 
  GROUP BY DEPTNO )A 
  LEFT JOIN
 
  (SELECT B.DEPTNO,COUNT(*) AS 人数 FROM (
  SELECT A.* FROM 
  (
  SELECT E.*,AVG(SAL)OVER(PARTITION BY DEPTNO) AS 平均工资, SAL -AVG(SAL)OVER(PARTITION BY DEPTNO) AS 高于平均 FROM EMP E)A
  WHERE A.高于平均 >0 )B
  GROUP BY B.DEPTNO) C
  
  ON A.DEPTNO =C.DEPTNO

捕获111.PNG


12.统计emp表中的各部门人数和所有部门人数总和,格式如下:
部门      人数
10          3
20          5
30          6
50          1
总计        15 

分析:只有两列,而且有汇总。 按部门分组对部门人数求和。

--方法一: 思路在于两张表Union all上下合并
 SELECT to_char(deptno) AS 部门,
        COUNT(DISTINCT empno) AS 人数
   FROM emp
  GROUP BY deptno
 UNION ALL
 SELECT '总计' AS 部门,
        COUNT(DISTINCT empno) AS 人数
   FROM emp;
-- 直接对要分组的字段Rollup(函数).
SELECT NVL(to_char(deptno),'总计') AS 部门,
        COUNT(DISTINCT empno) AS 人数
   FROM emp
  GROUP BY rollup(deptno);

13.查询在1980-1982年之间,每年雇用的人数,展示各式如下
  total    1980   1981   1982  
     13     1     10      2   

分析: 同上要求,再进一部的行列转换,用Pivot函数。既可以用Between..And..,也可以用IN(1980,1981,1982)

WITH T AS (  
 SELECT TO_CHAR(HIREDATE, 'YYYY') AS 年,
        COUNT(1) AS 人数
   FROM EMP
  WHERE TO_CHAR(HIREDATE, 'YYYY') BETWEEN '1980' AND '1982'
  GROUP BY ROLLUP(TO_CHAR(HIREDATE, 'YYYY')))
 SELECT * FROM T PIVOT(SUM(人数) FOR 年 IN(NULL AS total,'1980','1981','1982'));          


蜗牛
27 声望13 粉丝