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
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'));
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。