本文共 7688 字，大约阅读时间需要 25 分钟。

1  SELECT * FROM table  LIMIT [offset,] rows | rows OFFSET offset

 

   在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了上面这样一个功能。

 

   LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

mysql> SELECT * FROM table LIMIT 5,10; //检索记录行6-15 

 

   //为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

   //如果只给定一个参数，它表示返回最大的记录行数目： 

   mysql> SELECT * FROM table LIMIT 5;     //检索前 5 个记录行

   //换句话说，LIMIT n 等价于 LIMIT 0,n。

 

2  查找字符串'10,A,B' 中逗号','出现的次数cnt

 

由于 SQLite 中没有直接统计字符串中子串出现次数的函数，因此本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题，属于技巧题，即先用replace函数将原串中出现的子串用空串替换，再用原串长度减去替换后字符串的长度，最后除以子串的长度（本题中此步可省略，若子串长度大于1则不可省）。

 

SELECT (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt

 

还可以利用OJ系统的Bug直接输出2次来通过测试

SELECT 2 AS cnt

replace函数的用法：

replace('A','b','c') 用c替换字符串A中所有的b

 

3  获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

 

select first_name

from employees 

order by substr(first_name,-2)

 

select first_name

from employees 

order by substr(first_name,length(first_name)-1,length(first_name))

4  按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees  

 

本题要用到SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。

 

SELECT dept_no, group_concat(emp_no) AS employees

FROM dept_emp GROUP BY dept_no

 

5  查找排除当前最大、最小salary之后的员工的平均工资avg_salary

 

select avg(salary）  as avg_salary 

from salaries 

where salary not in 

(select  max(salary)  from  salaries 

 union 

 select  min(salary)  from  salaries)  

and to_date='9999-01-01'

 

6  存在如下的视图：

create view emp_v as select * from employees where emp_no >10005;

CREATE TABLE `employees` (

`emp_no` int(11) NOT NULL,

`birth_date` date NOT NULL,

`first_name` varchar(14) NOT NULL,

`last_name` varchar(16) NOT NULL,

`gender` char(1) NOT NULL,

`hire_date` date NOT NULL,

PRIMARY KEY (`emp_no`));

获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。

 

根据题意，不能使用 INTERSECT 关键字，但由于视图 emp_v 的记录是从 employees 中导出的，因此要判断两者中相等的数据，只需要判断emp_no相等即可。

方法一：用 WHERE 选取二者 emp_no 相等的记录

 

SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no

方法二：由于emp_v的全部记录均由 employees 导出，因此可以投机取巧，直接输出 emp_v 所有记录

 

SELECT * FROM emp_v

 

7  查找最晚入职员工的所有信息

 

select * from employees order by hire_date desc limit 0,1

 

查找入职员工时间排名倒数第三的员工所有信息

select * from employees order by hire_date desc limit 2,1

 

8  按照salary的累计和running_total，其中running_total为前面所有员工的salary累计和，其他以此类推。

 

select s1.emp_no,s1.salary,(select sum(s2.salary) from salaries s2 where s1.emp_no >=s2.emp_no 

                            and s2.to_date='9999-01-01' ) 

as running_total

from salaries s1

where s1.to_date='9999-01-01' 

order by s1.emp_no

 

9  获取所有员工当前的manager，如果当前的manager是自己的话结果不显示

 

select  d.emp_no, dm.emp_no as manager_no

from dept_emp d ,dept_manager dm

where d.to_date='9999-01-01'

and dm.to_date='9999-01-01'

and d.dept_no=dm.dept_no 

and d.emp_no !=dm.emp_no

 

10  查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

 

select s.emp_no,max(s.salary),e.last_name,e.first_name

from  employees e ,salaries s

where e.emp_no=s.emp_no

and s.to_date='9999-01-01'

and s.salary < (select max(salary) from salaries)

 

11  查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号

 

select salaries.*,dept_manager.dept_no from salaries,dept_manager 

where salaries.emp_no = dept_manager.emp_no 

and salaries.to_date='9999-01-01' 

and dept_manager.to_date = '9999-01-01'

 

12  查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按

 

select e.emp_no,s.salary 

from employees e,salaries s

where e.emp_no=s.emp_no

and e.hire_date=s.from_date ################重要

order by s.emp_no desc 

 

13  查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

 

解析：由于有些员工可能没有分配部门号，需要用左外连接就好了，即返回左表中所有的行，即便右表没有满足的条件

 

select a.last_name,a.first_name,b.dept_no from employees a left join dept_emp b on a.emp_no=b.emp_no

 

14 获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',

结果第一列给出员工的emp_no，

第二列给出其manager的manager_no，

第三列给出该员工当前的薪水emp_salary,

第四列给该员工对应的manager当前的薪水manager_salary

 

 

本题主要思想是创建两张表（一张记录当前所有员工的工资，另一张只记录部门经理的工资）进行比较，具体思路如下：

1、先用INNER JOIN连接salaries和demp_emp，建立当前所有员工的工资记录sem

2、再用INNER JOIN连接salaries和demp_manager，建立当前所有员工的工资记录sdm

3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary找出同一部门中工资比经理高的员工，并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary

 

select sem.emp_no as emp_no,sdm.emp_no as manager_no,sem.salary as emp_salary,sdm.salary as manager_salary  

from 

(select s.emp_no,s.salary,de.dept_no from salaries s ,dept_emp de where s.emp_no=de.emp_no 

 and s.to_date='9999-01-01') 

as sem,

(select s.emp_no,s.salary,dm.dept_no from salaries s ,dept_manager dm where s.emp_no=dm.emp_no 

 and s.to_date='9999-01-01')

as sdm

where sem.dept_no=sdm.dept_no

and sem.salary >sdm.salary

 

15  查找所有已经分配部门的员工的last_name和first_name

 

select e.last_name,e.first_name,d.dept_no 

from employees e,dept_emp d 

where e.emp_no = d.emp_no

 

16  查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

 

select emp_no,count(emp_no) as t

from salaries s

group by emp_no having t >15

 

17  找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

 

select distinct salary

from salaries

where to_date='9999-01-01'

order by salary desc

 

#### disticit 

 

18   获取所有非manager的员工emp_no

 

select e.emp_no

from employees e

where e.emp_no not in (select d.emp_no from dept_manager d )

 

19  获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

 

select d.dept_no,s.emp_no ,max(s.salary) as salary 

from dept_emp d ,salaries s

where d.emp_no=s.emp_no

and d.to_date='9999-01-01'

and s.to_date='9999-01-01'

group by d.dept_no

 

20   统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

 

select t.title,avg(s.salary) as avg

from salaries s ,titles t

where s.emp_no = t.emp_no 

and s.to_date='9999-01-01'

and s.to_date=t.to_date

group by t.title

 

21  查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

 

select e.last_name,e.first_name,d.dept_name

from employees e  

left join dept_emp de 

on e.emp_no =de.emp_no

left join departments d

on d.dept_no=de.dept_no   

 

22  查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

 

select e.emp_no,(s.salary-ss.salary) as growth

from employees e,salaries s,salaries ss

where e.emp_no=s.emp_no

and e.emp_no=ss.emp_no

and s.to_date='9999-01-01'

and ss.from_date=e.hire_date

order by growth asc

 

23  统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum

 

select d.dept_no,d.dept_name,count(salary)as  sum

from departments d,dept_emp de,salaries s

where d.dept_no=de.dept_no 

and de.emp_no=s.emp_no

group by d.dept_no

 

24 从titles表获取按照title进行分组，每组个数大于等于2，给出ti

 

select title, count(distinct emp_no) as t

from titles

group by title having t>=2

 

输出描述:

 

title	t
Assistant Engineer	2
Engineer	3
省略	省略
Staff	3

 

25  员工薪水排序

 

对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

 

select s1.emp_no,s1.salary,  count(distinct s2.salary) as rank

from salaries s1 ,salaries s2

where s1.to_date='9999-01-01' 

and s2.to_date='9999-01-01'

and s1.salary <=s2.salary

group by s1.emp_no

order by s1.salary desc ,s1.emp_no asc

 

26  给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'

 

select s.emp_no,e.first_name,e.last_name,eb.btype,s.salary,(

    case eb.btype

when 1 then s.salary*0.1

when 2 then s.salary*0.2

else s.salary*0.3 end) as bonus

from salaries s inner join emp_bonus eb on s.emp_no= eb.emp_no 

inner join employees e on s.emp_no=e.emp_no 

and s.to_date='9999-01-01'

 

其实观察测试数据会发现 btype 只有1,2,3三种情况，即使不会 CASE 表达式，也能运用四则运算解出：（注意要除以10.0，如果除以10的话，结果的小数位会被舍去）

  

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, 

(s.salary * b.btype / 10.0) AS bonus

FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no

INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

 

转载地址：http://qftgn.baihongyu.com/