# 前言

【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

## 一、如何让结果集中的重复数据只显示一次

SQL> select job,case when lag(job)over(order by a.job,ename)=job then null else job end as 职位,ename as 姓名
2  from emp a
3  order by a.job,a.ename;

JOB       职位      姓名
--------- --------- ----------
ANALYST   ANALYST   FORD
ANALYST             SCOTT
CLERK               JAMES
CLERK               MILLER
CLERK               SMITH
MANAGER   MANAGER   BLAKE
MANAGER             CLARK
MANAGER             JONES
PRESIDENT PRESIDENT KING
SALESMAN  SALESMAN  ALLEN
SALESMAN            MARTIN
SALESMAN            TURNER
SALESMAN            WARD
test

15 rows selected

SQL> 

SQL> select  case when lag(job)over(order by a.job,ename)=job then null else job end job,ename
2  from emp a
3  order by job,a.ename;

JOB       ENAME
--------- ----------
ANALYST   FORD
MANAGER   BLAKE
PRESIDENT KING
SALESMAN  ALLEN
CLARK
JAMES
JONES
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
test

15 rows selected

## 二、部门之间计算工资差异时也可以用行转列pivot

SQL> select deptno,sum(sal) as sm from emp where deptno is not null group by deptno ;

DEPTNO         SM
------ ----------
10       8750
20      10875
30       9400

SQL> select (select sum(sal) from emp where deptno = 20) -
2         (select sum(sal) from emp where deptno = 10) as d20_10,
3         (select sum(sal) from emp where deptno = 20) -
4         (select sum(sal) from emp where deptno = 30) as d20_30
5    from dual;

D20_10     D20_30
---------- ----------
2125       1475

SQL>
Plan Hash Value  :

------------------------------------------------------------------------
| Id  | Operation              | Name | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    1 |       |   14 | 00:00:01 |
|   1 |   SORT AGGREGATE       |      |    1 |     7 |      |          |
| * 2 |    TABLE ACCESS FULL   | EMP  |    5 |    35 |    3 | 00:00:01 |
|   3 |     SORT AGGREGATE     |      |    1 |     7 |      |          |
| * 4 |      TABLE ACCESS FULL | EMP  |    3 |    21 |    3 | 00:00:01 |
|   5 |   SORT AGGREGATE       |      |    1 |     7 |      |          |
| * 6 |    TABLE ACCESS FULL   | EMP  |    5 |    35 |    3 | 00:00:01 |
|   7 |     SORT AGGREGATE     |      |    1 |     7 |      |          |
| * 8 |      TABLE ACCESS FULL | EMP  |    6 |    42 |    3 | 00:00:01 |
|   9 |   FAST DUAL            |      |    1 |       |    2 | 00:00:01 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("DEPTNO"=20)
* 4 - filter("DEPTNO"=10)
* 6 - filter("DEPTNO"=20)
* 8 - filter("DEPTNO"=30)

SQL> with t as
2  （
3  select deptno, sum(sal) as sm
4    from emp
5   where deptno in (10, 20, 30)
6   group by deptno ）
7             select d20_sm - d10_sm, d20_sm - d30_sm
8               from t
9             pivot(max(sm) as sm
10                for deptno in(10 as d10, 20 as d20, 30 as d30));

D20_SM-D10_SM D20_SM-D30_SM
------------- -------------
2125          1475

Plan Hash Value  : 3261863285

-------------------------------------------------------------------------
| Id  | Operation               | Name | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    1 |    39 |    4 | 00:00:01 |
|   1 |   VIEW                  |      |    1 |    39 |    4 | 00:00:01 |
|   2 |    SORT AGGREGATE       |      |    1 |    26 |      |          |
|   3 |     VIEW                |      |    3 |    78 |    4 | 00:00:01 |
|   4 |      SORT GROUP BY      |      |    3 |    21 |    4 | 00:00:01 |
| * 5 |       TABLE ACCESS FULL | EMP  |   13 |    91 |    3 | 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - filter("DEPTNO"=10 OR "DEPTNO"=20 OR "DEPTNO"=30)

## 三、如何对已有数据进行分组打印

ADAMS      ALLEN      BLAKE      CLARK      FORD
JAMES      JONES      KING       MARTIN     MILLER
SCOTT      SMITH      TURNER     WARD       test

1. 生成序号
SQL> with t as
2   (select rownum as rn, ename from (select ename from emp order by ename))--第一步，根据名称进行字段顺序排序，排序后取rownum值，因为我想按照字段顺序打印
3   select * from t
4  ;

RN ENAME
---------- ----------
2 ALLEN
3 BLAKE
4 CLARK
5 FORD
6 JAMES
7 JONES
8 KING
9 MARTIN
10 MILLER
11 SCOTT
12 SMITH
13 TURNER
14 WARD
15 test

15 rows selected
1. 通过ceil函数把数据分为几个组
SQL>
SQL> with t as
2   (select rownum as rn, ename from (select ename from emp order by ename)),--第一步，根据名称进行字段顺序排序，排序后取rownum值，因为我想按照字段顺序打印
3  t1 as
4   (select ceil(rn / 5) as gp, ename from t)--我想一页展示五列
5    select * from t
6  ;

RN ENAME
---------- ----------
2 ALLEN
3 BLAKE
4 CLARK
5 FORD
6 JAMES
7 JONES
8 KING
9 MARTIN
10 MILLER
11 SCOTT
12 SMITH
13 TURNER
14 WARD
15 test

15 rows selected
1. 给各组数据生成序号
SQL> with t as
2   (select rownum as rn, ename from (select ename from emp order by ename)),--第一步，根据名称进行字段顺序排序，排序后取rownum值，因为我想按照字段顺序打印
3  t1 as
4   (select ceil(rn / 5) as gp, ename from t),--我想一页展示五列
5  t2 as
6   (select gp, ename, row_number() over(partition by gp order by ename) as rnn--给每一列编一个序号，便于行转列进行识别
7      from t1)
8  select *
9    FROM t2
10  ;

GP ENAME             RNN
---------- ---------- ----------
1 ALLEN               2
1 BLAKE               3
1 CLARK               4
1 FORD                5
2 JAMES               1
2 JONES               2
2 KING                3
2 MARTIN              4
2 MILLER              5
3 SCOTT               1
3 SMITH               2
3 TURNER              3
3 WARD                4
3 test                5

15 rows selected
1. 通过分组生成序号,并进行行转列
SQL>
SQL> with t as
2   (select rownum as rn, ename from (select ename from emp order by ename)),--第一步，根据名称进行字段顺序排序，排序后取rownum值，因为我想按照字段顺序打印
3  t1 as
4   (select ceil(rn / 5) as gp, ename from t),--我想一页展示五列
5  t2 as
6   (select gp, ename, row_number() over(partition by gp order by ename) as rnn--给每一列编一个序号，便于行转列进行识别
7      from t1)
8  select *
9    FROM t2
10  pivot (max(ename) as e for rnn in(1 as d1,
11                               2 as d2,
12                               3 as d3,
13                               4 as d4,
14                               5 as d5));

GP D1_E       D2_E       D3_E       D4_E       D5_E
---------- ---------- ---------- ---------- ---------- ----------
1 ADAMS      ALLEN      BLAKE      CLARK      FORD
2 JAMES      JONES      KING       MARTIN     MILLER
3 SCOTT      SMITH      TURNER     WARD       test

## 四、放假安排团队分组值班，如何快速进行人员分组？

SQL> select ntile(5)over(order by empno) as gp,ename from emp;

GP ENAME
---------- ----------
1 test
1 SMITH
1 ALLEN
2 WARD
2 JONES
2 MARTIN
3 BLAKE
3 CLARK
3 SCOTT
4 KING
4 TURNER
5 JAMES
5 FORD
5 MILLER

15 rows selected

# 总结

|
21小时前
|

10 0
|
21小时前
|

11 0
|
2天前
|
SQL 安全 数据库

11 0
|
6天前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL（3）-DQL（基本查询、条件查询、聚合函数、分组查询、排序查询、分页查询、案例练习）
MySQL数据库——SQL（3）-DQL（基本查询、条件查询、聚合函数、分组查询、排序查询、分页查询、案例练习）
13 0
|
8天前
|
SQL 数据库

|
17天前
|
SQL HIVE
【Hive SQL 每日一题】分组排名取值

18 1
|
20天前
|
SQL 关系型数据库 数据库

80 2
|
21天前
|
SQL 关系型数据库 MySQL

33 5
|
22天前
|
SQL 关系型数据库 MySQL
sql性能优化及实战
sql性能优化及实战
24 0
|
23天前
|
SQL 关系型数据库 MySQL
SQL基础开发与应用-课程及场景介绍

38 0