牛客sql刷题记录（更新中）

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

方法一：

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

LIMIT m,n : 表示从第m+1条开始，取n条数据；
LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。
本题limit 0,1 表示从第（0+1）条数据开始，取一条数据，即取出最晚入职员工。

方法二，更周到，因为最晚入职会不止一条。可能时间日期是一样的。

select * from employees
where hire_date=(
select max(hire_date) from employees)

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

嗯如果是接着上一个题目的修正版本：

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

很简单就过了。但是其实是有歧义的。
不过如果要去重啥的好像要复杂一些
以及用row_number之类怎么定义这个倒数第三，先不管他了。

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

select b.*,a.dept_no
from salaries b
inner join dept_manager a
on a.emp_no=b.emp_no
where a.to_date='9999-01-01'
and b.to_date='9999-01-01'

2个易错点：两个to_date都要限制，防止数据错误
主表salary放在前面，salary 和dept表的顺序换掉会跑不过，但我倒是不觉得有啥问题？

另外where改成and 连在on后面也能过

4.查找所有已经分配部门的员工的last_name和first_name以及dept_no

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

简单题

5.查找所有员工的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

在4的基础上改成left就好

6.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

select  a.emp_no,b.salary
from employees a
inner join salaries b
on a.emp_no=b.emp_no
and a.hire_date=b.from_date
order by a.emp_no desc

入职时候的薪水情况 所以有个 a.hire_date=b.from_date
话说写 and 和where应该都行吧，on and效率高一点，这个可以再查一下。但据说实习的时候能写on就不写where了。

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

select emp_no,count(distinct salary)-1 as t
from salaries
group by emp_no
having count(distinct salary)>15

比较合理的话应该是这样，不过通不过，这题目里不是算差值，有一条就算涨幅了。。emm 改成这样就通过了。
然后涨幅也不要管是不是比上一次怎样了，就算count就好了。这题是这样。

select emp_no,count(distinct salary) as t
from salaries
group by emp_no
having count(distinct salary)>=15

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

select distinct salary 
from salaries
where to_date='9999-01-01'
order by salary desc

楼上都是用distinct，但是大表一般用distinct效率不高，大数据量的时候都禁止用distinct，建议用group by解决重复问题。
对于distinct与group by的使用: 1、当对系统的性能高并数据量大时使用group by 2、当对系统的性能不高时使用数据量少时两者皆可 3、尽量使用group by

select salary 
from salaries
where to_date='9999-01-01'
group by salary
order by salary desc

9.获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'

select a.dept_no,a.emp_no,b.salary
from dept_manager a
left join salaries b
on a.emp_no=b.emp_no
where a.'to_date'='9999-01-01'
and b.'to_date'='9999-01-01'

left改成inner也可以通过，不过现实工作中，有的领导没有工资应该填写null去排查。

10.获取所有非manager的员工emp_no

select a.emp_no
from employees a
left join  dept_manager b
on a.emp_no=b.emp_no
where  b.emp_no is null

11.获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。

结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。

select a.emp_no as emp_no,b.emp_no as manager_no
from dept_emp a
left join dept_manager b
on a.dept_no=b.dept_no
where a.to_date='9999-01-01'
and b.to_date='9999-01-01'
and a.emp_no <> b.emp_no

容易错的 一个是命名
一个是最后一句如果当前的manager是自己的话结果不显示
这里的不显示是指当部门主管是自己的时候，整行都不显示；不是不显示部门经理的编号。
这道题用INNER JOIN 和 LEFT JOIN 是都可以通过的，因为默认职员表中每个职员都是分配了部门的，因此保留不保留职员表(左表)没有关联的行(实际上都关联了)结果都一样

另外一个不错的关于on和where 的解释

通不过：

SELECT de.emp_no, dm.emp_no AS manager_no
FROM dept_emp AS de
LEFT JOIN dept_manager AS dm
ON de.dept_no = dm.dept_no
AND de.to_date = '9999-01-01' AND dm.to_date = '9999-01-01' 
AND de.emp_no NOT IN (SELECT dm.emp_no FROM dept_manager)  - 这里的and改成where可以通过，因为用on的话经理也会显示，只是显示null，这里的不显示是指当部门主管是自己的时候，整行都不显示，所以要过滤掉

通过了：

SELECT de.emp_no, dm.emp_no AS manager_no
FROM dept_emp AS de
INNER JOIN dept_manager AS dm
ON de.dept_no = dm.dept_no
AND de.to_date = '9999-01-01' AND dm.to_date = '9999-01-01'
AND de.emp_no NOT IN (SELECT dm.emp_no FROM dept_manager)

这里涉及ON和WHERE的用法。

使用连接操作，关联两张或多张表时，数据库会生成并返回一张临时表。

LEFT JOIN 模式下，ON 和 WHERE 过滤条件的区别（ RIGHT JOIN、FULL JOIN 同理）：
ON 条件是在生成临时表时使用的条件，不管 ON 中的条件是否为真，都会返回左边表中的记录；
WHERE 条件是在临时表已经生成后，对临时表进行的过滤条件，如果 WHERE 条件不为真，记录就会被过滤掉。

INNER JOIN 只返回同时存在于两张或多张表的行数据，所以过滤条件放在 ON 中或 WHERE 中，返回的结果一样。

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

能通过但是有问题：
问题在于emp_no
同时select了b.dept_no,a.emp_no，但是group by 第一个b.dept_no
那会出现一个部门有多条emp_no，但最后筛选出来的是默认第一条， 而非max对应的那一条。

这个问题其实也包括了我曾经困扰的：
select 多字段 ，但是只group by 其中某些字段
有时候会出错的， 因为都会出现默认第一条。
有些时候我们可以用max或者sum来筛选。

select b.dept_no,a.emp_no,max(a.salary) as salary
from salaries a
inner join dept_emp b
on a.emp_no=b.emp_no

where a.to_date='9999-01-01'
and b.to_date='9999-01-01'
group by 1

修改成，三表匹配：

select c.dept_no,d.emp_no,c.max_salary
from
    (select b.dept_no,max(a.salary) as max_salary
    from salaries a
    inner join dept_emp b
    on a.emp_no=b.emp_no
    where a.to_date='9999-01-01'
    and b.to_date='9999-01-01'
    group by 1)c
inner join salaries d
on c.max_salary=d.salary
inner join dept_emp e
on d.emp_no=e.emp_no
and c.dept_no=e.dept_no
where d.to_date='9999-01-01'
and e.to_date='9999-01-01'
order by c.dept_no asc

另外一个更常用，不容易出错的方法，就是窗口函数

select c.dept_no,c.emp_no,c.salary
from
    (select a.dept_no,b.emp_no,b.salary,
    rank() over(partition by a.dept_no order by b.salary desc) as ranking
    from dept_emp a
    inner join salaries b
    on a.emp_no=b.emp_no
    where a.to_date='9999-01-01' and b.to_date='9999-01-01'
    )c

where c.ranking=1

13.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

so easy

select title,count(*) as t
from titles
group by 1
having count(*)>=2

14.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略。

我是这样写的，不过看了大家的讨论有一些争议。
争议在于有人说distinct是对后面所有的起作用的。

select title,count(*) as t
from 
(select distinct emp_no,title from titles)
group by 1
having count(*)>=2

答案是

select  title ,count(distinct emp_no) as t
from titles 
 
group by title 
having t >= 2

distinct 与group by 去重

mysql中常用去重复数据的方法是使用 distinct 或者group by ，以上2种均能实现，但2者也有不同的地方。

distinct 特点：

如：select distinct name， sex，from tb_students 这个sql的语法中，查询 tb_students 表中 name， sex，并去除名字和性别都重复的学生：

1、distinct 只能放在查询字段的最前面，不能放在查询字段的中间或者后面。

备注：select sex，distinct name from tb_students 这种写法是错误的，distinct 只能写在所有查询字段的前面

2、distinct 对后面所有的字段均起作用，即 去重是查询的所有字段完全重复的数据，而不是只对 distinct 后面连接的单个字段重复的数据。

备注：也就是 distinct 关键字对 name， sex 都起作用，去重姓名、性别完全一样的学生，如果姓名相同、性别不同是不会去重的。

3、要查询多个字段，但只针对一个字段去重，使用distinct去重的话是无法实现的。

group by 特点：

1、一般与聚类函数使用（如count()/sum()等），也可单独使用。

2、group by 也对后面所有的字段均起作用，即 去重是查询的所有字段完全重复的数据，而不是只对 group by后面连接的单个字段重复的数据。

3、查询的字段与group by 后面分组的字段没有限制。

特别说明：在oracle中使用group by时，查询的字段必须是group by 分组的字段和聚类函数。如select name,sex from tb_students group by name这个sql

语法在oracle中是错误的，因为sex 不在group by 分组后面；但在mysql中是支持的。

14.查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

select *
from employees
where emp_no%2=1
and last_name<>'Mary'
order by hire_date desc

mod(emp_no,2)=1本应该也可以。但是可能牛客的库不支持。

15.统计出当前各个title类型对应的员工当前（to_date='9999-01-01'）薪水对应的平均工资。结果给出title以及平均工资avg。

select b.title,avg(a.salary)
from salaries a inner join
titles b
on a.emp_no=b.emp_no
where a.to_date='9999-01-01'
and b.to_date='9999-01-01'
group by 1

16.获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

select  a.emp_no,a.salary 
from

(select emp_no,salary,
    dense_rank()over (order by salary desc)as ranking
from salaries)a
where a.ranking=2

dense_rank 是11112的类型。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 205,132评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,802评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,566评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,858评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,867评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,695评论 1赞 282
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,064评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,705评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,915评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,677评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,796评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,432评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,041评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,992评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,223评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,185评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,535评论 2赞 343

推荐阅读更多精彩内容

  • 2018-06-14
    在C语言中,五种基本数据类型存储空间长度的排列顺序是: A)char B)char=int<=float C)ch...
    夏天再来阅读 3,323评论 0赞 2
  • SQL练习
    查找最晚入职员工的所有信息 LIMIT m,n : 表示从第m+1条开始，取n条数据；LIMIT n ： 表示从第...
    扣篮的左手阅读 270评论 0赞 0
  • bash
    官网 中文版本 好的网站 Content-type: text/htmlBASH Section: User ...
    不排版阅读 4,365评论 0赞 5
  • 动起来就好11
    昨晚下载了keep，第一次认认真真地锻炼，为了可怜的大身板。 三十分钟的练习，累得腰酸背痛气喘吁吁，强撑到结束，我...
    自由最美阅读 218评论 0赞 3
  • DO、PO、BO、DTO、VO等概念
    POJO 全称为：Plain Ordinary Java Object，普通的 java 对象，一般用在数据层映射...
    聪明的奇瑞阅读 15,169评论 2赞 14