牛客网sql实战题解析

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- 方法一：这种不规范，
-- 只适用于倒数第三只有一位员工或虽有多位但只需要选其中一位员工的情况
select *
from employees
order by hire_date desc
limit 2,1 -- limit的offset从0开始数

-- 方法二：如果倒数第三有多位员工或为了适应性更广
select *
from employees
where hire_date = (
                      select distinct hire_date
                      from employees
                      order by hire_date desc
                      limit 2,1
                  )
-- 加了distinct会去重分组，多个相同入职日期会分为一组
-- 注意distinct会先于limit执行

-- 方法三：用窗口函数
select emp_no
,birth_date
,first_name
,last_name
,gender
,hire_date
from
(
    select *
    ,dense_rank()over(order by hire_date desc) r
    from employees
) a
where r = 3

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 这题用内连接即可，
-- employees的员工如果连不上部门表的员工（里面的员工id都分配了部门id），
-- 那自然是没有被分配部门，
-- 使用左连接然后筛选是多此一举
select last_name
,first_name
,dept_no
from employees e
join dept_emp d
on e.emp_no = d.emp_no

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 方法一：排除法
select emp_no
from employees
where emp_no not in (
                        select emp_no from dept_manager
                    )

-- 方法二：表连接再选取法
select e.emp_no
from employees e
left join dept_manager d
on e.emp_no = d.emp_no
where dept_no is null

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

# 方法一：先取员工当前薪资表，再获取部门最高薪资表。
# 然后用薪资作为筛选条件对应以获取正确的员工编号。
SELECT t2.dept_no, t1.emp_no, t2.max_salary
FROM
(
    SELECT e.emp_no , e.dept_no , s.salary 
    FROM dept_emp e
    JOIN salaries s
    ON e.emp_no=s.emp_no
) t1
JOIN
(
    SELECT e.dept_no , MAX(salary) max_salary
    FROM dept_emp e
    JOIN salaries s
    ON e.emp_no=s.emp_no
    GROUP BY e.dept_no
) t2
ON t1.dept_no=t2.dept_no
WHERE t1.salary=t2.max_salary
ORDER BY t2.dept_no

# 方法二：窗口函数
select dept_no
,emp_no
,salary maxSalary
from
    (
        select d.dept_no
        ,d.emp_no
        ,salary
        ,dense_rank()over(partition by d.dept_no order by salary desc) r
        from dept_emp d
        join salaries s
        on d.emp_no = s.emp_no
    ) a
where r = 1
order by dept_no

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 与SQL2一样，这类问题统称为获取倒数或顺数第n的xx，
-- 第一种方法是用order by与limit，第二种是窗口函数
-- 方法一：严谨版（通过distinct来去重排行）
select emp_no
,salary
from salaries
where salary = (
                    select distinct salary from salaries
                    order by salary desc
                    limit 1,1
                )

-- 方法二：使用窗口函数
select emp_no
,salary
from
(
    select *
    ,dense_rank()over(order by salary desc) r
    from salaries
) a
where r = 2

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

-- 此题与SQL17一样，只是要求不能使用order by完成，
-- 但第n多/少相当于n个select的max/min的嵌套，
-- 增加一个新方法：自连接排序法
-- 方法一：剥离
select e.emp_no
,s.salary
,last_name
,first_name
from employees e
join salaries s
on e.emp_no = s.emp_no
and s.salary =
(
    select max(salary) -- 第二多
    from salaries
    where salary !=
    (
        select max(salary) -- 第一多
        from salaries
    )
)

-- 方法二：自连接
select e.emp_no
,s.salary
,last_name
,first_name
from employees e
join salaries s
on e.emp_no=s.emp_no 
and s.salary = 
(
     select s1.salary
     from salaries s1
     join salaries s2 -- 自连接
     -- 与比它大或等于的值连接，这样它有几个不同值即大于等于多少个值，即排行第几
     on s1.salary<=s2.salary
     group by s1.salary
     having count(distinct s2.salary) = 2
 )
 
 
-- v2.0这样直接与自连接结果表join更简洁
select e.emp_no
,a.salary
,last_name
,first_name
from employees e
join 
(
     select s1.emp_no,s1.salary
     from salaries s1
     join salaries s2 -- 自连接
     -- 与比它大或等于的值连接，这样它有几个不同值即大于等于多少个值，即排行第几
     on s1.salary<=s2.salary
     group by s1.emp_no,s1.salary
     having count(distinct s2.salary) = 2
 ) a
 on e.emp_no=a.emp_no

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- "查找<在职>员工<自入职以来>的薪水涨幅情况"，
-- 说明终点工资一定是to_date为9999-01-01的salary
-- 中间工资起伏不要紧，只需要求相对于入职时(hire_date)的涨幅就行了
select e.emp_no
,(s2.salary-s1.salary) as growth
from employees e
join salaries s1
on e.emp_no = s1.emp_no
and s1.from_date = e.hire_date
join salaries s2
on e.emp_no = s2.emp_no
and s2.to_date = '9999-01-01'
order by growth

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 方法一：自连接（自表连接，左表一列会连接右表所有列，同SQL18）
select s1.emp_no
,s1.salary
,count(distinct s2.salary) t_rank -- 被筛s2有几个，代表第几大
from salaries s1,salaries s2
where s1.salary <= s2.salary -- 晒出比s1大的s2的salary列
group by 1
order by s1.salary desc,emp_no

-- 方法二：窗口函数
select emp_no
,salary
,dense_rank()over(order by salary desc) t_rank -- 注意要用dense_rank
from salaries
order by t_rank,emp_no

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 方法一：排除非manager的员工即可
select d.dept_no
,e.emp_no
,s.salary
from employees e
join dept_emp d
on e.emp_no = d.emp_no
join salaries s
on e.emp_no = s.emp_no
where e.emp_no not in (
    select emp_no from dept_manager
)

-- 方法二（不需要employees，因为dept_manager除去manager就是其他员工了）
select d.dept_no
,d.emp_no
,s.salary
from dept_emp d
join salaries s
on d.emp_no = s.emp_no
where d.emp_no not in (
    select emp_no from dept_manager
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

select d.emp_no
,m.emp_no manager_no
,s1.salary emp_salary
,s2.salary manager_salary
from dept_emp d
join dept_manager m
on d.dept_no = m.dept_no
and d.emp_no != m.emp_no -- 排除d.emp_no=m.emp_no的对应的正是普通员工与其经理
join salaries s1 -- 于是这里对应的是普通员工的薪水
on d.emp_no = s1.emp_no
join salaries s2
on m.emp_no = s2.emp_no -- 于是这里对应经理的薪水
where s1.salary > s2.salary -- 普通员工比经理薪水高的情况

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

select name
,count(fc.film_id) num
from film_category fc
join category c
on fc.category_id =c.category_id
join film f
on fc.film_id = f.film_id
where description like '%robot%'
and fc.category_id in -- 该分类包含电影总数量>=5部
(
    select category_id
    from film_category
    group by 1
    having count(film_id) >= 5
)
group by 1

1
2

-- 如果存在，则忽略 insert ignore（mysql写法）
INSERT IGNORE INTO actor VALUES('3','ED','CHASE','2006-02-15 12:34:33');

1
2
3
4
5
6
7

-- 这是属于SQL高级特征-对字段进行约束的内容了，对外键进行约束的语法为：
-- ALTER TABLE <表名>
-- ADD CONSTRAINT FOREIGN KEY (<作为外键的列名>)
-- REFERENCES <关联的表>(<关联的列>)
ALTER TABLE audit
ADD CONSTRAINT FOREIGN KEY (emp_no)
REFERENCES employees_test(id)

1
2

alter table audit add constraint fk_audit_employees
foreign key audit(emp_no) references employees_test(id);

1
2
3
4
5

create table audit(
    emp_no int not null,
    create_date datetime not null,
    foreign key(emp_no) references employees_test(id)
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 注意 ' 要转义，有两种转义方法

-- 方法一
select concat(last_name,'\'',first_name) from employees;

-- 方法二
select concat(last_name,'''',first_name) from employees;

-- 或者请出双引号
select concat(last_name,"'", first_name)  from employees;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 方法一：把逗号去除后计算长度减少了多少
select id,length(string)-length(replace(string,',','')) from strings;
-- replace(字符串，“需要替换的子串”，“用于替换子串的字符串”）

-- 方法二：把数字字母去除后计算长度
SELECT
    id,
    length(
        regexp_replace(STRING, '[A-Z0-9]', '')
    )
FROM
    strings;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- SUBSTR( )与SUBSTRING( )意思相等，第一个字符的位置为都1
-- 方法一
select first_name
from employees
order by right(first_name,2)

-- 方法二
select first_name
from employees
order by substring(first_name,-2) -- 为负数，返回从倒数第2个位置到字符串结尾的子字符串
-- 注意，如果第三个参数省略，则直接截取到末尾

-- 方法三
select first_name
from employees
order by substring(first_name,length(first_name)-1)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 利用group_concat函数
select dept_no
,group_concat(emp_no) employees
from dept_emp
group by dept_no

-- 或者这样。合并的字段分隔符默认为逗号，可通过参数separator指定
select dept_no
,group_concat(emp_no separator ',') employees
from dept_emp
group by dept_no

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- exists意思是存在，逐条比对返回满足条件的
select * from employees e
where not exists (
    select d.dept_no from dept_emp d
    where d.emp_no = e.emp_no);

-- 如果填的是exists，且后面匹配成功了，就是有部门的员工，加个not就是没有部门的员工

-- 换句话说，本题意为在 employees 中挑选出令
-- select d.dept_no from dept_emp d where d.emp_no = e.emp_no不成立的记录

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- 方法一：子查询法
select s1.emp_no
,s1.salary
,
(
    select sum(s2.salary) from salaries s2
    where s2.emp_no <= s1.emp_no -- 把小于等于的都加起来就是了
    and s2.to_date = '9999-01-01'
) running_total
from salaries s1
where s1.to_date = '9999-01-01'
order by s1.emp_no

-- 方法二：自连接法
select s1.emp_no
,s1.salary
,sum(s2.salary) running_total
from salaries s1
join salaries s2
on s1.emp_no >= s2.emp_no
where s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
group by s1.emp_no
order by s1.emp_no

-- 方法三：窗口函数
select emp_no
,salary
,sum(salary)over(order by emp_no) running_total
from salaries
where to_date = '9999-01-01'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

-- 方法一：自连接法类似
select e1.first_name
from employees e1
where (
          select count(*) -- 得到的结果是e1.first_name该行值的实际排名
          from employees e2
          where e1.first_name >= e2.first_name
      )%2 = 1

-- 方法二：窗口函数法
-- （不行，可能会打乱原顺序，除非原表是按emp_no升序排列的）
# select first_name
# from
# (
#     select emp_no
#     ,first_name
#     ,row_number()over(order by first_name) r
#     from employees
# ) a
# where r%2 = 1
# order by emp_no

-- 修正版窗口函数
SELECT
    e.first_name
FROM employees e JOIN
(
    SELECT 
        first_name
        , ROW_NUMBER() OVER(ORDER BY first_name ASC) AS  r_num
    FROM employees
) AS t 
ON e.first_name = t.first_name
WHERE t.r_num % 2 = 1;
-- 这里用原表连接窗口函数结果，就不打乱顺序了

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 利用group by与case when的组合拳
select date
,round(sum(case type when 'no_completed' then 1 else 0 end) / count(*),3) p
from email e
join user u1
on e.send_id = u1.id
and u1.is_blacklist = 0
join user u2
on e.receive_id = u2.id
and u2.is_blacklist = 0
group by date
order by date

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

-- 方法一：先查出最近登录对应的用户信息，然后把额外信息连过去
select u.name u_n
,c.name c_n
,l.date date
from login l
join
(
select user_id
,max(date) d
from login
group by 1
) a -- 用户最近登录表
on l.user_id = a.user_id
and l.date = a.d
join client c
on l.client_id = c.id
join user u
on l.user_id = u.id
order by u_n

-- 方法一.一：这种是方法一的改良，实际上，
-- 同一用户的客户端client.name相同，所以对user.name分组后没有影响结果，
-- 即便是非聚合字段也没事（group by默认取非聚合字段的第一条，这只有一条）
-- 由于在标准语法中select子句中不能出现group by子句中未出现的列名，
-- 这个在标准语法要求中通不过
select u.name u_n
,c.name c_n
,max(date)
from user u
join login l
on u.id = l.user_id
join client c
on l.client_id = c.id
group by u.name
order by u.name

-- 方法二：联结查询
select u.name,c.name,l1.date
from login l1,user u,client c
where l1.date=(select max(l2.date) from login l2 where l1.user_id=l2.user_id)
and l1.user_id=u.id
and l1.client_id=c.id
order by u.name

-- 方法三：窗口函数，全表联结，然后用窗口函数选个排第一的
select u_n
,c_n
,date
from
(
select b.name u_n
,c.name c_n
,date
,rank()over(partition by b.name order by date desc) r
from login a
join user b
on a.user_id = b.id
join client c
on a.client_id = c.id
) a
where r = 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- 方法一：表连接
select round(count(*)/(select count(distinct user_id) from login),3) p
from login l
join
(
select user_id
,min(date) first_date -- 最小的登录日期
from login
group by user_id
) f
on l.user_id = f.user_id
where date = DATE_ADD(f.first_date,INTERVAL 1 DAY) -- 筛出比最小的大一天的记录

-- 方法二：表连接（稍微修改）
select round(count(l.id)/count(f.user_id),3) p
from login l
right join -- 各唯一用户id及最小登录日期表作为主表
(
select user_id
,min(date) first_date
from login
group by user_id
) f
on l.user_id = f.user_id
and l.date = DATE_ADD(f.first_date,INTERVAL 1 DAY)
-- 用right join将各唯一用户id及最小登录日期表作为主表，
-- round部分就不用进行更多子查询了，l.id数量即满足条件用户，f.user_id即全部用户

-- 方法三：子查询
select round(count(*)/(select count(distinct user_id) from login),3) p
from login l
where (user_id,date) in (
                            select user_id
                            ,DATE_ADD(min(date),INTERVAL 1 DAY)
                            from login
                            group by user_id
                        )

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

select 
round(count(if(datediff(a_date,b_date)=1,c.user_id,null))/count(*),3) p
from
(
select a.user_id
,a.date a_date
,b.date b_date
,row_number()over(partition by a.user_id order by a.date,b.date) r
from
(
select user_id
,date
from login
group by user_id,date
) a
join a as b
on a.user_id=b.user_id
and a.date<b.date
) c
where c.r=1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

-- 方法一：先获得各唯一用户id及最小登录日期表，
-- 与原表匹配，各最小登录日期有几个，该日期就有几个用户登录了，
-- 为了得到登录用户数为0的日期，用left join保留所有date
select date
,count(first_date) new
from login l
left join
(
    select
    user_id
    ,min(date) first_date
    from login
    group by user_id
) a
on l.user_id = a.user_id
and l.date = a.first_date
group by date

-- 方法二：窗口函数，排序后数下各日期有几个number one就可以
select date
,sum(case when r = 1 then 1 else 0 end)
from
(
    select *
    ,rank()over(partition by user_id order by date) r
    from login
) a
group by date
order by date

-- 方法三：联结查询机灵法
-- 对各date，满足case when中条件计数板就加1，外层用group by汇总计数板
select date,sum(t) from
(
    select date
    ,case when (user_id,date) in 
    (select user_id,min(date) from login group by user_id)
    then 1
    else 0
    end as t
    from login
) a
group by date
order by date

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

-- 方法一
SELECT a.date, ROUND(COUNT(b.user_id) * 1.0/COUNT(a.user_id), 3) AS p
FROM (
    SELECT user_id, MIN(date) AS date
    FROM login
    GROUP BY user_id) a
LEFT JOIN login b
ON a.user_id = b.user_id
AND b.date = date(a.date, '+1 day')
GROUP BY a.date
UNION
SELECT date, 0.000 AS p
FROM login
WHERE date NOT IN (
    SELECT MIN(date)
    FROM login
    GROUP BY user_id)
ORDER BY date;

-- 方法二（尚未验证）
sele
,c.user_id
round(count(if(datediff(a_date,b_date)=1,c.user_id,null))/count(*),3) p
from
(
select a.user_id
,a.date a_date
,b.date b_date
,row_number()over(partition by a.user_id order by a.date,b.date) r
from
(
select user_id
,date
from login
group by user_id,date
) a
join a as b
on a.user_id=b.user_id
and a.date<b.date
) c
where c.r=1
group by c.user_id

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

select u.name u_n
,l.date
,sum(p.number)over(partition by u.name order by l.date)
from login l
join passing_number p
on l.user_id = p.user_id
and l.date = p.date
join user u
on l.user_id = u.id
order by 2,1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

--  方法一
select job
,round(case when s%2=0 then s/2 else (s+1)/2 end,0) start
,round(case when s%2=0 then s/2+1 else (s+1)/2 end,0) end
from
(
    select job
    ,count(*) s
    from grade
    group by job
) a
order by job

-- 方法二（简化）
-- 偶数的round(s/2)与奇数的round(s/2)对算开头是一样的 round(4/2)=2 round(5/2)=3
-- 偶数的round((s+1)/2与奇数的round(s+1)/2对算结尾是一样的 round(4+1/2)=3 round(5+1)/2=3
select job
,round(round(s/2)) start
,round(round(s+1)/2) end
from
(
    select job
    ,count(*) s
    from grade
    group by job
) a
order by job

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

-- 方法一：我的普通思路
select id
,job
,score
,t_rank
from
(
    select g.id
    ,g.job
    ,g.score
    ,rank()over(partition by g.job order by g.score desc) t_rank
    ,start
    ,end
    from grade g
    join
    (
        select job
        ,round(case when s%2=0 then s/2 else (s+1)/2 end,0) start
        ,round(case when s%2=0 then s/2+1 else (s+1)/2 end,0) end
        from
        (
            select job
            ,count(*) s
            from grade
            group by job
        ) a
    ) b
    on g.job = b.job
) c
where t_rank in (start,end)
order by id

-- 方法二：大佬妙解
select id
,job
,score
,s_rank
from
(select *
        ,(row_number()over(partition by job order by score desc))as s_rank
        ,(count(score)over(partition by job))as num
        from grade) t1
where abs(t1.s_rank - (t1.num+1)/2) < 1 -- 无论奇偶，中位数的位置距离（个数+1）/2 小于1
order by id

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- 方法一：普通法
select * from
order_info
where user_id in
(
    select user_id
    from order_info
    where date > '2025-10-15'
    and status = 'completed'
    and product_name in ('C++','Java','Python')
    group by user_id
    having count(status) >= 2
)
and status = 'completed'
and product_name in ('C++','Java','Python')
and date > '2025-10-15'
order by id

-- 方法二：窗口函数
select id
,user_id
,product_name
,status
,client_id
,date
from 
(
    select *
    ,count(*)over(partition by user_id) cnt
    from order_info
    where date > '2025-10-15'
    and status = 'completed'
    and product_name in ('C++','Java','Python')
) a
where cnt >= 2
order by id