牛客网SQL题汇总

牛客SQL总结

建表

SQL36

create table actor_name
(
first_name varchar(45) not null,
last_name varchar(45) not null
) as 
select first_name,last_name
from actor

SQL39

select *
from salaries
force index (idx_emp_no)
where emp_no=10005

SQL40

ALTER TABLE actor ADD create_date datetime NOT NULL DEFAULT '2020-10-01 00:00:00'

SQL41（触发器）

create trigger audit_log 
after insert on employees_test
for each row
begin 
    insert into audit values(new.id,new.name);
end

SQL46

ALTER TABLE audit
ADD CONSTRAINT FOREIGN KEY (emp_no)
REFERENCES employees_test(id);

查询

最值

SQL2

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

主页limit第一参数为(页数 - 1)*每页数量

去重

SQL8

SELECT
    DISTINCT salary
FROM
    salaries
ORDER BY
    salary desc

多表查询

SQL10

SELECT
    employees.emp_no
FROM
    employees
left JOIN
    dept_manager
ON
    dept_manager.emp_no = employees.emp_no
WHERE
    dept_manager.emp_no is null

SQL11

SELECT
    e.emp_no,
    m.emp_no as manager
FROM
    dept_emp e
JOIN
    dept_manager m
on
    e.dept_no = m.dept_no
WHERE
    e.emp_no != m.emp_no
group BY
    e.dept_no

SQL12

select 
    t.dept_no, 
    s.emp_no, 
    t.maxSalary
from 
    (
    select
        d.dept_no, 
        max(s.salary) as maxSalary 
    from 
        dept_emp d,
        salaries s
    where 
        d.emp_no = s.emp_no 
    group by 
        d.dept_no
    ) as t, 
    salaries s, 
    dept_emp d
where 
    t.maxSalary = s.salary and 
    t.dept_no = d.dept_no and 
    d.emp_no = s.emp_no
order by 
    t.dept_no asc

SQL19

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
    de.dept_no = d.dept_no

SQL21

-- 本题应该考虑降薪的问题:题中没有讲员工不降薪，所以不能直接使用最大薪水-最小薪水求值
SELECT 
  s1.emp_no,
  s2.salary - s1.salary AS growth 
FROM
  (SELECT 
    e.emp_no,
    s.salary 
  FROM
    employees e 
  LEFT JOIN salaries s 
      ON e.emp_no = s.emp_no 
  		WHERE e.hire_date = s.from_date) AS s1  -- 入职薪水
  INNER JOIN 
    (SELECT 
      e.emp_no,
      s.salary 
    FROM
      employees e 
    LEFT JOIN salaries s 
        ON e.emp_no = s.emp_no 
    		WHERE s.to_date = '9999-01-01'
    ) AS s2  -- 现在薪水
    ON s1.emp_no = s2.emp_no  
ORDER BY growth 

SQL22

SELECT
    d.dept_no,
    d.dept_name,
    t.sum
FROM
    (
     SELECT
        de.dept_no,
        COUNT(*) as "sum"
     FROM
        dept_emp de
     JOIN
        salaries s
     ON
        de.emp_no = s.emp_no
     GROUP BY
        de.dept_no
    ) as t
JOIN
    departments d
ON
    d.dept_no = t.dept_no

SQL25

SELECT
    de.emp_no,
    t.manager_no,
    s.salary as emp_salary,
    t.manager_salary
from
    dept_emp de
JOIN
    (
    SELECT
        dm.emp_no as manager_no,
        dm.dept_no,
        s.salary as manager_salary
    FROM
        dept_manager dm
    JOIN
        salaries s
    ON
        dm.emp_no = s.emp_no
    ) as t
ON
    t. dept_no = de.dept_no
JOIN
    salaries s
ON
    de.emp_no = s.emp_no
WHERE
    s.salary > t.manager_salary

SQL57

select * from employees e
where not exists
(select emp_no from dept_emp d where d.emp_no = e.emp_no);

SQL59

SELECT 
    e.emp_no, 
    e.first_name, 
    e.last_name, 
    eb.btype, 
    s.salary,
    CASE 
        WHEN eb.btype = 1 THEN s.salary * 0.1
        WHEN eb.btype = 2 THEN s.salary * 0.2
        ELSE s.salary * 0.3
    END bonus
FROM 
    emp_bonus AS eb, 
    employees AS e, 
    salaries AS s
WHERE 
    eb.emp_no = e.emp_no AND 
    s.emp_no = e.emp_no AND 
    s.to_date='9999-01-01';

SQL64

SELECT
    p.id,
    p.name,
    t.content
from
    person p
left join
    task t
on
    p.id = t.person_id

SQL82

SELECT
    o.id,
    o.is_group_buy,
    case o.is_group_buy
        when 'No' then c.name
        else 'None'
    end client_name 
FROM
    order_info as o
JOIN
    (
    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(*) >= 2
    ) as t
    ON
        o.user_id = t.user_id
left join
    client as c
    on
        o.client_id = c.id
where
    o.date > '2025-10-15' and
    o.status = 'completed' and
    o.product_name in ('C++', 'Java', 'Python')

SQL83

select
    case o.client_id
        when 0 then 'GroupBuy'
        else c.name
    end source,
    o.cnt
FROM
    (
    select
        client_id,
        count(*) cnt
    from
        (
        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(*) >= 2
        ) as t,
        order_info
     where
         t.user_id = order_info.user_id and
         order_info.date > '2025-10-15' and
         order_info.status = 'completed' and
         order_info.product_name in ('C++', 'Java', 'Python')
     group by
         order_info.client_id
    ) o
left JOIN
    client as c
    ON
        o.client_id = c.id
order BY
    source

SQL86

-- 查询在2025年投递简历的每个岗位，每个月内收到简历的数量和，对应的2026年的同月同岗位收到简历的数量，最后的结果先按first_year_mon月份降序，再按job降序排序显示
SELECT h1.job,first_year_mon,first_year_cnt,second_year_mon,second_year_cnt 
FROM 
-- 2025年的 
(SELECT job,DATE_FORMAT(DATE,'%Y-%m') AS first_year_mon,SUM(num) AS first_year_cnt
FROM resume_info
WHERE DATE LIKE '2025%'  -- 符合最左前缀匹配原则，也走索引
GROUP BY job,first_year_mon) AS h1
-- inner join 假装优化下，inner可以省略
INNER JOIN 
-- 2026年的
(SELECT job,DATE_FORMAT(DATE,'%Y-%m') AS second_year_mon,SUM(num) AS second_year_cnt
FROM resume_info
WHERE DATE LIKE '2026%'  -- 符合最左前缀匹配原则，也走索引
GROUP BY job,second_year_mon) AS h2  
-- 表连接条件：两表job相同且月份相同，
-- 因date日期类型经过 DATE_FORMAT()后变成 字符串，所以使用right()函数取后两位即为月数
ON h1.job=h2.job AND  right(first_year_mon,2)=right(second_year_mon,2) 
-- 排序
ORDER BY first_year_mon DESC,h1.job DESC;

SQL90

select 
    user.id, 
    user.name,
    t.grade_sum 
from
    (
    select 
        user_id,
        sum(grade_num) as grade_sum 
    from 
        grade_info 
    group by 
        user_id
    having 
        grade_sum =(
            select 
                sum(grade_num) as grade_sum 
            from 
                grade_info
            group by
                user_id
            order by 
                grade_sum desc limit 1
        )
    ) t
join
    user
    on t.user_id=user.id
order by
    user.id

SQL91

select 
    user.id, 
    user.name,
    t.grade_sum 
from
    (
        select 
            user_id,
            sum(case type when 'add' then grade_num else -grade_num end) as grade_sum 
        from 
            grade_info 
        group by user_id
        having 
            grade_sum=(
                select 
                    sum(case type when 'add' then grade_num else -grade_num end ) as grade_sum 
                from 
                    grade_info 
                group by 
                    user_id 
                order by 
                    grade_sum desc 
                limit 1) 
    ) t
join user
on t.user_id=user.id
order by user.id 

排名序号

SQL23

SELECT
    s1.emp_no,
    s1.salary,
    COUNT(DISTINCT s2.salary) as t_rank
FROM
    salaries s1
JOIN
    salaries s2
		ON
    	s1.salary <= s2.salary
GROUP BY
    s1.emp_no
ORDER BY
    t_rank

SQL63

SELECT
    p1.id,
    p1.number,
    count(distinct p2.number) as t_rank
FROM
    passing_number p1
JOIN
    passing_number p2
ON
    p1.number <= p2.number
GROUP BY
    id, number
order BY
    t_rank

SQL81

SELECT
    a.user_id , 
    a.first_buy_date , 
    c.date , 
    a.cnt
FROM
    (
    SELECT 
        user_id , 
        MIN(date) first_buy_date , 
        COUNT(*) cnt
    FROM 
        order_info
    WHERE 
        date > '2025-10-15'
    AND status = 'completed'
    AND product_name IN ('C++' , 'Java' , 'Python')
    GROUP BY 
        user_id
    HAVING 
        COUNT(*) >= 2
    ) a
JOIN 
    (
    SELECT 
        user_id , 
        date
    FROM
        (
        SELECT 
            user_id ,
            date , 
            RANK() OVER(PARTITION BY user_id ORDER BY date) rnk
        FROM 
            order_info
        WHERE 
            date > '2025-10-15'
        AND status = 'completed'
        AND product_name IN ('C++' , 'Java' , 'Python')
        ) b
     WHERE 
        b.rnk = 2
     ) c
ON 
    a.user_id = c.user_id
ORDER BY 
    a.user_id;

SQL87

select grade, sum(number) over(order by grade) t_rank 
from class_grade 
order by grade;

函数

聚合函数

SQL16

SELECT
    t.title,
    avg(s.salary)
FROM
    titles t
JOIN
    salaries s
ON
    t.emp_no = s.emp_no
GROUP BY
    t.title
ORDER BY
    avg(s.salary)

SQL54

SELECT
    AVG(salary)
FROM
    salaries
WHERE
    to_date = '9999-01-01' and
    salary not in (
        select
            max(salary)
        from
            salaries
        where to_date = '9999-01-01'
    ) and
    salary not in(
        select
            min(salary)
        from
            salaries
        where to_date = '9999-01-01'
    )

SQL62

select number from grade group by number having count(*)>=3

系统函数

SQL32

SELECT
    CONCAT(last_name, ' ', first_name)
from
    employees

SQL51

SELECT
    LENGTH("10,A,B") - LENGTH(REPLACE("10,A,B", ",", "")) as cnt;

SQL52

SELECT
    first_name
FROM
    employees
ORDER BY
    SUBSTR(first_name, LENGTH(first_name) - 1, LENGTH(first_name));

SQL53

select dept_no, group_concat(emp_no) as employees
from dept_emp
group by dept_no

SQL60

# 方法一
SELECT s1.emp_no, s1.salary,
(SELECT SUM(s2.salary)
FROM salaries AS s2
WHERE s2.emp_no <= s1.emp_no
AND s2.to_date = '9999-01-01') AS running_total
FROM salaries AS s1
WHERE s1.to_date = '9999-01-01'
ORDER BY s1.emp_no

# 方法二
select
    emp_no,
    salary,
    sum(salary) over(order by emp_no) as running_total
from
    salaries
where
    to_date = '9999-01-01'

SQL65

select 
    email.date, 
    round(
        sum(
            case email.type 
                when'completed' then 0 
                else 1 
            end) * 1.0 / count(email.type), 3) as p
from 
    email
join
    user as u1 
on 
    email.send_id=u1.id and 
    u1.is_blacklist=0
join 
    user as u2 
on 
    email.receive_id=u2.id and 
    u2.is_blacklist=0
group by 
    email.date 
order by 
    email.date;

SQL69

select 
    distinct date,
    sum(
        case 
            when (user_id,date) in 
                (select 
                     user_id,
                     min(date)
                 from 
                     login 
                 group by user_id)
            then 1 
            else 0 
        end)
from login
group by date
order by date;

SQL70

select 
    date,
    ifnull(
        round(
            (sum(
                case 
                    when (user_id,date) in
                        (select 
                             user_id,
                             date_add(date,interval -1 day) 
                         from login)  and 
                        (user_id,date)in 
                        (select 
                             user_id,
                             min(date)
                         from login 
                         group by user_id)
                    then 1 
                    else 0 
                end))/
            (sum(
                case 
                    when (user_id,date)in
                        (select 
                             user_id,
                             min(date)
                         from login 
                         group by user_id)
                    then 1 
                    else 0 
                end)),3),0)as p
from login
group by date
order by date;

SQL85

-- 按月统计数量并排序
select job,date_format(date,'%Y-%m') as mon,sum(num) as cnt
from resume_info
where date like '2025%'  -- 符合最左前缀匹配原则，也走索引
group by job,mon
order by mon desc,cnt desc;

窗口函数

SQL61

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;

SQL68

select 
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);

SQL71

select u.name as u_n
        ,p.date as date
        ,sum(p.number)over(partition by u.name order by date)as ps_num
from passing_number p,user u
where p.user_id=u.id
order by p.date,u.name;

SQL74

SELECT a.id, l.name, a.score
FROM
language AS l
JOIN
(SELECT id, language_id, score, dense_rank()over(PARTITION BY language_id ORDER BY score DESC) AS rank_num
FROM grade)a
ON l.id = a.language_id
WHERE rank_num <=2
ORDER BY l.name, a.score DESC, a.id;

SQL76

select 
    id
    ,g.job as job
    ,score
    ,rk
from (
    select 
        id
        ,job
        ,score
        ,row_number() over(
            partition by job
            order by score desc
        ) as rk
    from grade 
) g

join (
    select
        job
        ,floor((count(id) + 1)/ 2) as m1
        ,ceiling((count(id) + 1)/ 2) as m2
    from grade
    group by job
) m

on g.job = m.job

where rk between m1 and m2          -- 过滤，保证row_number处于中位数位置

order by id;

SQL88

select grade
from
    (select grade,
    (select sum(number) from class_grade) as total,
    sum(number)over(order by grade) a, -- 求正序
    sum(number)over(order by grade desc) b  -- 求逆序
     from class_grade
    order by grade)t
where a >= total/2 and b >= total/2  -- 正序逆序均大于整个数列数字个数的一半
order by grade;

SQL89

select 
    u.name, 
    g.grade
from
    (
    select 
        user_id, 
        sum(grade_num)over(partition by user_id) grade
    from 
        grade_info
    order by 
        grade desc
    limit 1
    )g
join
    user as u
    on g.user_id = u.id

分页

SQL55

SELECT
    *
FROM
    employees
limit 5, 5

插入

SQL34

INSERT INTO actor(actor_id,
                  first_name,
                  last_name,
                  last_update)
VALUES(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
      (2,'NICK','WAHLBERG','2006-02-15 12:34:33');

SQL35

# mysql中常用的三种插入数据的语句:
# insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
# replace into表示插入替换数据，需求表中有PrimaryKey，
#             或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
# insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");

删除

SQL42

DELETE
    t2.*
FROM
    `titles_test` t1,
    `titles_test` t2
WHERE
    t1.emp_no = t2.emp_no and
    t1.id < t2.id

更新

SQL48

UPDATE
    salaries
SET
    salary = salary + salary * 0.1
WHERE
    emp_no in (
        select
            emp_no
        from
            emp_bonus
    ) and
    to_date = '9999-01-01'