只是甲的博客

文章目录概述一.检索记录1.1 从表中检索所有行和列1.2 从表中检索部分行1.3 查找满足多个条件的行1.4 从表中检索部分列1.5 为列取有意义的名称概述《SQL.Cookbook》是迄今为止，笔者遇到过sql系列最全、案例最精简的一本书。许多真实环境遇到的棘手问题，本书都提供了解决方案。许多面试的sql题目(无论是Oracle、MySQL还是HIVE)，本书也提供了解决方案。如果想提升自己的sql编写能力，本书是不二之选。笔者陆陆续续的看过《SQL.Cookbook》多次，也从中收获颇多，近段

备注:测试数据库版本为MySQL 8.0文章目录一.需求二.解决方案2.1 分解求出it_apps各部门及员工2.2 it_apps与it_research进行拼接测试数据:create table it_research(deptno int, ename varchar(20));insert into it_research values (100,'HOPKINS');insert into it_research values (100,'JONES');insert into it

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求给表中的值分等级，然后把结果集转置为三列，其思想是将最高的3挡作为一列、次高的3挡作一列，其余作一列。例如，祥哥表EMP中的员工按SAL分等级，然后把结果集转置为三列。其结果集如下所示:二.解决方案这个解决方案的关键是先使用窗口函数 DENSE_RANK OVER，按SAL给员工分等级，同时允许捆绑。使用DENSE_RANK OVER，可以很容易地看到最高的

备注:测试数据库版本为MySQL 8.0测试数据:create table zqs(id int,str varchar(1000));insert into zqs(id,str) values (1,'【京东】abc【中国电信】');insert into zqs(id,str) values (1,'【京东】abc【中国电信】def');insert into zqs(id,str) values (1,'****【京东】abc【中国电信】def');insert into zqs(id

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求确定给定行属于哪些类型的节点: 叶节点、分支节点及根节点。对于这个例子，叶节点表示该员工不是经理；分支节点上的员工即是经理，又有经理根节点是没有经理的员工。通过返回1(TRUE)或0(FALSE),二.解决方案请注意，EMP表是树状层次模型，而不是递归层次模型，根节点的mgr值为null。如果emp是递归层次模型的话，根节点应有自引用(即员工KING的mgr值

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求找到直接及简介(即JONES下属的下属)为JONES工作的所有员工。JONES下属的员工列表如下所示:±------±-----+| ename | lvl |±------±-----+| JONES | 1 || SCOTT | 2 || FORD | 2 || ADAMS | 3 || SMITH | 3 |±

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求返回一个结果集，它描述整个表的层次。在EMP表中，员工KING没有经理，所以KING是根节点。从KING开始，显示KING下面的所有员工以及KING下属的所有员工(如存在)。最后，返回下列结果集:±-----------------------------+| emp_tree |±------------------

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求员工CLARK为KING工作，假设员工CLARK又是另一个员工的经理，怎么办?select ename,empno,mgrfrom empwhere ename in (‘KING’,‘CLARK’,‘MILLER’);±-------±------±-----+| ename | empno | mgr |±-------±------±----

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求将父记录的信息跟只记录中的数据放在一起。例如,显示每个员工的姓名及其经理的姓名。返回的结果集:±----------------------+| emps_and_mgrs |±----------------------+| SMITH woks for FORD || ALLEN woks for BLAKE || WARD wo

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求要创建一个包含小计的报表，然后对接过做转置变换，使报表更易读。例如，要求创建一个报表，它显示每个部门、部门经理，以及这些经理手下员工的总工资。另外，还要返回两个小计:每个部门中各经理手下员工工资的总和、结果集中所有工资总和(部门小计的总和)。目前有如下报表:±-------±-----±---------+| deptno | mgr | sal

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求要计算移动聚集。例如，求表EMP中工资的移动和，要从第一个员工的hiredate开始，计算任何90天内的总和，以观察从雇佣第一个员工至雇佣最后一个员工期间90天开销的波动情况。应返回以下结果集:二.解决方案窗口函数的子句中指定可移动窗口，这个问题就很容易解决。关键是需要在窗口函数中按hiredate排序，然后指定一个从雇佣第一个员工开始的90天窗口。如果无法

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求同时按不同维进行聚集。例如，要返回这样的结果集:列出每个员工的名字、他所在的部门、该部门的员工数(包括他自己)、与他有同样职位的员工数(也包括他自己)以及emp表中的员工总数。起结果集应该如下所示:±-------±-------±-----------±----------±--------±------+| ename | deptno | deptno_

备注:测试数据库版本为MySQL 8.0测试数据准备:drop table trx_log;create table trx_log(trx_id int,trx_date timestamp,trx_cnt int);insert into trx_log values (1,'2020-10-28 19:03:07',44);insert into trx_log values (2,'2020-10-28 19:03:08',18);insert into trx_log values

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求把一列中的值(如EMP表中的JOB列)映射成一列“布尔”标记。例如，希望如下返回:±-------±---------±---------±-------±-----------±--------+| ename | is_clerk | is_sales | is_mgr | is_analyst | is_prez |±-------±---------±

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求对JOB/DEPTNO的每种组合，求按deptno和job的总工资。并求表EMP中所有工资的总计。返回的结果集应如:±-------±----------±------------------------±---------+| deptno | job | category | sal |±-------±-

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求定义一个“简单小计”结果集，它包含一列的聚集值以及全表的总计值。例如将表emp中各job的工资总和放入一个结果集，并且将表emp总所有工资的总和也加入其中。按job的工资是小计，而表emp中的工资总和是总计。这样的结果集应该如下:±----------±---------+| job | sal |±----------±-------

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求正在执行一个group by查询，并希望返回哪些属于选择列表而不包含于group by子句的列。通常，这是不可能的，因为对于这样的非组列，并不时每行都包含唯一值。假设要找到每个部门中工资最高和最低的员工，以及每种职位中工资最高和最低的员工，要查看这些人的姓名、所在部门、职位名称以及工资。希望返回的结果集如下:±-------±-------±----------

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求生成一个从底部向上延伸的直方图。例如，采用纵向直方图显示每个部门的职员数，一个星号"*"表示一个员工。返回结果集应该如:±-----±-----±-----+| d10 | d20 | d30 |±-----±-----±-----+| * | * | * || * | * | * || * | *

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求使用SQL生成横向延伸的直方图。例如，采用横向直方图显示每个部门的志愿书，一个星号"*"表示一个员工。返回的结果集应该如:±-------±-------+| deptno | cnt |±-------±-------+| 10 | *** || 20 | ***** || 30 | ****** |±-----

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求把数据变为固定数目的桶。例如，把表EMP中的员工编组为4桶。其结果集应该如下所示:±-----±------±-------+| grp | empno | ename |±-----±------±-------+| 1 | 7566 | JONES || 1 | 7788 | SCOTT || 1 | 7900 | J

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求把数据编组成大小均匀的桶，每桶都包含预定的元素数。桶的总数可能不确定，但要确保每桶都包含5个元素。例如，依据empno值把表emp中的员工分为5个一组，下面给出了结果集:±-----±------±-------+| grp | empno | ename |±-----±------±-------+| 1 | 7369 | SMITH |

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求对来自多个行的数据进行计算。为便于计算，可以把这些行转置到列中，这样所有需要的值都包含于1行中。deptno 20 是总工资最高的部门，执行下列查询可以确认这一列:mysql> select deptno, sum(sal) as sal-> from emp-> group by deptno;±-------±-------

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求生成报表时如果两行的同一列包含相同值，希望这个值仅显示一次。例如，要从表emp中找出deptno和ename，按deptno给所有行分组，而且deptno仅显示一次。希望返回如下结果集:±-------±-------+| deptno | ename |±-------±-------+| 10 | CLARK || NULL | KIN

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求把查询中返回的所有列转换为1列。例如，返回deptno 10中所有员工的ename、job和sal，而且要把这3个值放到一列中。为每个员工返回3行信息，而且在两个员工之间加一个空白行。希望返回的结果集如下:±----------+| emps |±----------+| CLARK || MANAGER || 2450

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求把列转换为行。±----------±----------±----------+| deptno_10 | deptno_20 | deptno_30 |±----------±----------±----------+| 3 | 5 | 6 |±----------±----------±---------

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求要把行转换为列，根据原表给定列的每个值创建一个列。例如，返回每个员工及他们的职位(JOB)，目前的查询返回如下结果集:希望重新设置为结果集的格式，使每个职位使用一列:二.解决方案要解决本例的问题，必须使每个job/ename组合唯一，然后，在使用聚集函数去除null时，不会丢失ename。使用标量子查询，按empno给每个员工分等级。使用CASE表达式和聚集

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本文章目录一.需求二.解决方案2.1 解决方案12.2 解决方案2一.需求希望将几个行组中的数据转换成几行中的列，每个原来的行组转换成一行。例如，下面的结果集显示了每个部门中员工的数目:deptno cnt10 320 530 6希望重新设置输出格式，使其结果集看起来如下:dep

备注:测试数据库版本为MySQL 8.0测试数据:drop table t10;create table t10(id int,order_date date,process_date date);insert into t10 values (1,'2020-09-25','2020-09-27');insert into t10 values (2,'2020-09-26','2020-09-28');insert into t10 values (3,'2020-09-27','2020-

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求返回一个结果集，它包含每个部门中所有员工的姓名、所在部门、工资、聘用日期以及部门中最新聘用员工的工资。应返回下列结果集:±-------±-------±--------±-----------±-----------+| deptno | ename | sal | hiredate | latest_sal |±-------±-------±

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求在表EMP中查找不同的职位，但不想看到有重复。其结果集应该是:±----------+| job |±----------+| CLERK || SALESMAN || MANAGER || ANALYST || PRESIDENT |±----------+二.解决方案2.1 distinct 方法select d

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求给表EMP中的工资分等级，并允许捆绑，返回下列结果集:±----±--------+| rnk | sal |±----±--------+| 1 | 800.00 || 2 | 950.00 || 3 | 1100.00 || 4 | 1250.00 || 4 | 1250.00 || 5 | 1300.00 |

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求返回每个员工的姓名和工资以及低于自己的最高工资和高于自己的最低工资。如果没有更高或最低的工资，则可能要求结果环绕(即第一个SAL显示最后一个SAL，反之亦然。)要求输出如下:±-------±--------±--------±--------+| ename | sal | forward | rewind |±-------±--------±-

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求找到满足这样条件的员工: 即他的收入比紧随其后聘用的员工要少。二.解决方案首先定义“未来”的意思。必须给结果集排序，才能够定义一个值在另一个值"后面".2.1 子查询方法第一个子查询 查询后面所有聘用日期且工资比这一行工资多的聘用日期(如果没找到，返回就是null)第二个子查询 查询下一个聘用日期如果第一个和第二个相等，就代表满足题目要求。这个解题思路不错

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求查找表中的"两级"值。例如，找出表EMP中具有最高工资和最低工资的员工。二.解决方案2.1 子查询方法select ename from empwhere sal in ( (select min(sal) from emp), (select max(sal) from emp) );测试记录mysql> se

备注:测试数据库版本为MySQL 8.0如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本一.需求一某种排序方式，限定结果集只包含一定数目的记录。例如，返回最高5档工资的员工姓名和工资。二.解决方案这种解决方案的关键是两个步骤: 首先按预定方式给行排序，然后限定结果集，只包含感兴趣的行。2.1 标量子查询方法使用标量子查询，为每个工资创建一个等级。然后利用等级限制子查询的结果:select ename,sal from (select ( sele

备注:测试数据库版本为MySQL 8.0测试数据:drop table t2;create table t2(test1 int,test2 int);insert into t2 values (20,20);insert into t2 values (50,25);insert into t2 values (20,20);insert into t2 values (60,30);insert into t2 values (70,90);insert into t2 valu

备注:测试数据库版本为MySQL 8.0一.需求返回部门10 和 20中所有员工的姓名和部门信息，并返回部门30和40(但不包含员工信息)的部门信息。二.解决方案将 OR 条件移到 JOIN子句中:select e.ename, d.deptno, d.dname, d.locfrom dept dleft join emp eon (d.deptno = e.deptnoand (e.deptno = 10 or e.deptno = 20))order by 2;另外，还可以先用

备注:测试数据库版本为MySQL 8.0一.需求需要显示emp表中根据ename排序的基数行，跳过偶数行二.解决方案求出行号，然后通过mod取余函数跳过偶数行-- 传统写法select x.enamefrom (select e.ename, (select count(*) from emp d where d.ename <= e.ename) rnfrom emp eorder by e.ename) xwhere mod(rn,2) = 1;--

备注:测试数据库版本为MySQL 8.0一.需求给结果集分页或滚动显示整个结果集例如，先返回表emp中的前5行工资，然后是下5行，依此类推。目标是允许用户一次能够查看5个记录，每次单击"下一页"按钮都会向前桂东5个记录。二.解决方案在SQL中，由于没有“第一个”、“最后一个”及“下一个”的概念，所以必须对要处理的行按某种方式排序，只有如此，才会准确地返回一定范围内记录。-- MySQL limit offset解决方案-- offset表示跳过几行select salfrom emp

备注:测试数据库版本为MySQL 8.0一.需求mysql> select * from v;±--------±-----------±-----------+| proj_id | proj_start | proj_end |±--------±-----------±-----------+| 1 | 2020-10-01 | 2020-10-02 || 2 | 2020-10-02 | 2020-10-03 || 3 | 2020-10