SQL 练习2021.9.22

–1.使用EMP表
–(1) 查询每种职业的平均工资
SELECT DISTINCT JOB,ROUND(AVG(SAL) OVER(PARTITION BY JOB ),2) AVG_SAL
FROM EMP
ORDER BY AVG_SAL ;
–(2) 将第一步的查询结果展示为如下所示（5 种方法）
/*
办事员 销售 总裁 经理 分析员
1037.5 1400 5000 2758.33 3000
*/
–1.位移分析函数 lead

SELECT AVG_SAL 办事员,L1 销售,L2 总裁 ,L3 经理 ,L4 分析员
FROM(
SELECT A.*,LEAD(AVG_SAL,1) OVER(ORDER BY AVG_SAL) L1,
LEAD(AVG_SAL,4) OVER(ORDER BY AVG_SAL) L2 ,
round(LEAD(AVG_SAL,2) OVER(ORDER BY AVG_SAL),2) L3,
LEAD(AVG_SAL,3) OVER(ORDER BY AVG_SAL) L4
FROM (SELECT DISTINCT JOB,AVG(SAL) OVER(PARTITION BY JOB ) AVG_SAL
FROM EMP
ORDER BY AVG_SAL ) A)
where rownum = 1;
–2.原表查询
SELECT

  max(CASE WHEN JOB ='CLERK' then 1037.5 END) AS 办事员,
  max(CASE WHEN JOB ='SALESMAN' then 1400 END) AS 销售 ,
  max(CASE WHEN JOB ='PRESIDENT' then 5000 END) AS 总裁,
  max(CASE WHEN JOB ='MANAGER' then 2758 END) AS 经理,
  max(CASE WHEN JOB ='ANALYST' then 3000 END) AS 分析员

FROM (SELECT DISTINCT JOB,ROUND(AVG(SAL) OVER(PARTITION BY JOB ),2) AVG_SAL
FROM EMP
ORDER BY AVG_SAL );
–3. decode
SELECT
max(DECODE(JOB,‘CLERK’,1037.5)) AS 办事员,
max(DECODE(JOB,‘SALESMAN’,1400)) AS 销售,
max(DECODE(JOB,‘PRESIDENT’,5000)) AS 总裁,
max(DECODE(JOB,‘MANAGER’,2758)) AS 经理,
max(DECODE(JOB,‘ANALYST’,3000)) AS 分析员

FROM(SELECT DISTINCT JOB,ROUND(AVG(SAL) OVER(PARTITION BY JOB ),2) AVG_SAL
FROM EMP
ORDER BY AVG_SAL);

–4. 临时表
with x as (SELECT DISTINCT JOB,ROUND(AVG(SAL) OVER(PARTITION BY JOB ),2) AVG_SAL
FROM EMP
ORDER BY AVG_SAL)

SELECT AVG_SAL 办事员,L1 销售,L2 总裁 ,L3 经理 ,L4 分析员
FROM(
SELECT A.*,LEAD(AVG_SAL,1) OVER(ORDER BY AVG_SAL) L1,
LEAD(AVG_SAL,4) OVER(ORDER BY AVG_SAL) L2 ,
round(LEAD(AVG_SAL,2) OVER(ORDER BY AVG_SAL),2) L3,
LEAD(AVG_SAL,3) OVER(ORDER BY AVG_SAL) L4
FROM x A)
where rownum = 1;

–5.位移分析函数 lead
SELECT B.办事员,B.销售,B.总裁,B.经理,B.分析员
FROM
(
SELECT A.* ,LAG(AVG_SAL,4) OVER(ORDER BY AVG_SAL) 办事员,
LAG(AVG_SAL,3) OVER(ORDER BY AVG_SAL) 销售,
AVG_SAL 总裁，
LAG(AVG_SAL,2) OVER(ORDER BY AVG_SAL) 经理,
LAG(AVG_SAL,1) OVER(ORDER BY AVG_SAL) 分析员
FROM(
SELECT DISTINCT JOB,ROUND(AVG(SAL) OVER(PARTITION BY JOB ),2) AVG_SAL
FROM EMP
ORDER BY AVG_SAL) A) B
WHERE JOB = ‘PRESIDENT’;

–2.
CREATE TABLE ST1(
STU VARCHAR2(10),
COURSE VARCHAR2(20),
SCORE NUMBER
);
INSERT INTO ST1(STU,COURSE,SCORE) VALUES (‘S1’,‘语文’,80);
INSERT INTO ST1(STU,COURSE,SCORE) VALUES (‘S1’,‘数学’,70);
INSERT INTO ST1(STU,COURSE,SCORE) VALUES (‘S1’,‘英语’,60);
INSERT INTO ST1(STU,COURSE,SCORE) VALUES (‘S2’,‘语文’,90);
INSERT INTO ST1(STU,COURSE,SCORE) VALUES (‘S2’,‘数学’,80);
INSERT INTO ST1(STU,COURSE,SCORE) VALUES (‘S2’,‘英语’,100);
INSERT INTO ST1(STU,COURSE,SCORE) VALUES (‘S3’,‘英语’,80);
commit;
/* 表ST1
学生 科目 成绩
S1 语文 80
S1 数学 70
S1 英语 60
S2 语文 90
S2 数学 80
S2 英语 100
S3 英语 80
*/

----（1）通过SQL将 T1 表信息转换以下格式（写出 1 种即可）
/*
学生 语文 数学 英语 总分
S1 80 70 60 210
S2 90 80 100 270
S3 NULL NULL 80 80
/
SELECT A.STU 学生,A.SCORE 语文 ,A.L1 数学 ,A.L2 英语, (A.SCORE +NVL(A.L1,0)+NVL(A.L2,0)) 总分
FROM(
SELECT S.,
Lag(SCORE,1)OVER(PARTITION BY STU ORDER BY STU) L1,
Lag(SCORE,2)OVER(PARTITION BY STU ORDER BY STU) L2

FROM ST1 S) A
WHERE A.COURSE = ‘英语’
–也能做
–不对称用casewhen最简单

SELECT STU,
SUM(CASE WHEN COURSE = ‘语文’ THEN SCORE END) 语文,
SUM(CASE WHEN COURSE =‘数学’ THEN SCORE END) 数学,
SUM(CASE WHEN COURSE =‘数学’ THEN SCORE END) 英语,
SUM(SCORE)
FROM ST1 S
GROUP BY STU
----（2）通过SQL计算出T1表各科目分数最高的学生。字段：学科 分数 姓名
SELECT A.*
FROM (SELECT S.* ,MAX(SCORE) OVER(PARTITION BY COURSE) MAX_SC
FROM ST1 S) A
WHERE A.SCORE =MAX_SC

–3.
CREATE TABLE PROCESS_LOG (
CODE VARCHAR2(5),
STATUS VARCHAR2(20),
TIMES DATE
);
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A01’,‘BEGIN’,TO_DATE(‘2021-06-11’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A01’,‘GOING’,TO_DATE(‘2021-06-18’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A01’,‘FINISH’,TO_DATE(‘2021-06-20’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A02’,‘BEGIN’,TO_DATE(‘2021-05-01’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A02’,‘GOING’,TO_DATE(‘2021-05-04’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A03’,‘BEGIN’,TO_DATE(‘2021-07-01’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A03’,‘GOING’,TO_DATE(‘2021-07-04’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A03’,‘FINISH’,TO_DATE(‘2021-07-08’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A03’,‘ROLLBACK GOING’,TO_DATE(‘2021-07-11’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A04’,‘BEGIN’,TO_DATE(‘2021-03-01’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A04’,‘GOING’,TO_DATE(‘2021-03-05’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A04’,‘FINISH’,TO_DATE(‘2021-03-08’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A04’,‘ROLLBACK GOING’,TO_DATE(‘2021-03-09’,‘YYYY-MM-DD’));
INSERT INTO PROCESS_LOG(CODE,STATUS,TIMES) VALUES(‘A04’,‘FINISH’,TO_DATE(‘2021-03-11’,‘YYYY-MM-DD’));
COMMIT;
–(1)查出已经结束的进程（A01,A04）
SELECT CODE
FROM(
SELECT A.*,MAX(TIMES)OVER(PARTITION BY CODE ORDER BY TIMES DESC ) RID
FROM PROCESS_LOG A ) B
WHERE TIMES = RID AND STATUS = ‘FINISH’;

–（2）对于已结束的进程，请查出如下结果：
/*
编码 是否回退 开始时间 进行时间 结束时间
A01 否 2021/6/11 2021/6/18 2021/6/20
A04 是 2021/3/1 2021/3/9 2021/3/11
*/
SELECT CODE,
MAX(CASE WHEN STATUS = ‘ROLLBACK GOING’ THEN ‘是’ ELSE’否’ END) 是否回退,
MAX(CASE WHEN STATUS = ‘BEGIN’ THEN TIMES END) 开始时间,
MAX(CASE WHEN STATUS IN (‘GOING’,‘ROLLBACK GOING’) THEN TIMES END) 进行时间,
MAX(CASE WHEN STATUS = ‘FINISH’ THEN TIMES END) 结束时间

FROM PROCESS_LOG
WHERE CODE IN (SELECT CODE
FROM(
SELECT A.*,MAX(TIMES)OVER(PARTITION BY CODE ORDER BY TIMES DESC ) RID
FROM PROCESS_LOG A ) B
WHERE TIMES = RID AND STATUS = ‘FINISH’)
GROUP BY CODE
ORDER BY CODE ;
–4.
CREATE TABLE SALES (
YEARS NUMBER,
MONTH NUMBER,
SALES NUMBER
);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2019,1,2365);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2019,2,4525);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2019,3,6321);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2019,4,7564);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2019,5,2312);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2019,6,1314);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2019,7,7744);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2019,8,4231);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2019,9,7653);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2019,10,2623);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2019,11,7654);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2019,12,3121);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2020,1,1323);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2020,2,5363);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2020,3,1322);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2020,4,5356);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2020,5,2313);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2020,6,6543);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2020,7,8643);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2020,8,2759);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2020,9,2341);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2020,10,4753);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2020,11,6743);
INSERT INTO SALES(YEARS,MONTHS,SALES) VALUES(2020,12,6753);
COMMIT;

–(1)求出2020年的销售额同比
SELECT (L1-SUM_SALES)/SUM_SALES
FROM(
SELECT DISTINCT YEARS,
SUM(SALES) SUM_SALES,
LAG(SUM(SALES),1) OVER (ORDER BY YEARS) L1
FROM SALES
GROUP BY YEARS) A
WHERE YEARS = 2020;
–(2)求出每年每月的环比（1月份不需要求环比）
SELECT YEARS,MONTHS,(L1-SALES)/SALES 环比
FROM (
SELECT S.*,LAG(SALES,1)OVER(PARTITION BY YEARS ORDER BY MONTHS) L1
FROM SALES S)A