牛客网SQL

最新推荐文章于 2024-06-09 20:13:21 发布

Deng_Allen

最新推荐文章于 2024-06-09 20:13:21 发布

阅读量394

点赞数

分类专栏： SQL学习文章标签： sql

本文链接：https://blog.youkuaiyun.com/Deng_Allen/article/details/114262562

版权

SQL学习专栏收录该内容

6 篇文章

订阅专栏

本文记录了作者在2021年3月25日至4月15日进行的SQL刷题过程，涵盖了从基础查询到复杂操作的各种问题。包括查找最晚入职员工、非部门领导员工、薪水排名、薪水涨幅、索引创建、触发器设计等多个实战场景，深入探究了SQL在数据分析和数据库管理中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2021/3/25 ——2021/4/15 第二轮刷题

SQL1

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

# 方法1：使用子查询

--获取所有最晚的hire_date,然后把与其相等的记录选出来，可以选多条

-- date类型的数据越大，表示时间越接近现在，即最晚，使用MAX()函数

-- where语句使用=和in都可得到结果

SELECT *
FROM employees
WHERE hire_date = (
      SELECT MAX(hire_date)
      FROM employees);


# 方法2：使用limit或者offset关键字

--通过倒序排序，查询第一条数据，就是最晚入职的信息，但是最晚入职可能不止一人，可能存在同一时间入职多人

SELECT * FROM employees
ORDER BY hire_date DESC LIMIT 1;
#
select * from employees
order by hire_date desc limit 0,1;
#
select * from employees
order by hire_date desc limit 1 offset 0;

# 补充：
-- limit n：表示从第0条数据开始，读取n条数据，是limt(0, n)的缩写
-- limit m,n：表示从第m条数据开始，读取n条数据
-- limit n offset m：表示从第m条数据开始，读取n条数据

SQL2

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

--存在员工入职时间可能一致的问题


# 方法1：子查询(最佳)

select * from employees
where hire_date =(
    select distinct hire_date 
    from employees
    order by hire_date desc
    limit 1 offset 2);
#
select * from employees
where hire_date =(
    select hire_date from employees
    group by hire_date
    order by hire_date desc
    limit 1 offset 2);

# 补充：
# 若存在多人(如3人)同时在最晚的一天入职的情形，必须要考虑去重(使用distinct或者group by)
distinct和group by的执行顺序都在limit前面

# 方法2：使用limit或者offset关键字

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

-- 总结：
-- 方法1适用于存在多个员工入职同时达到最晚一天的情况。泛用性更好。

SQL8

找出所有员工当前薪水salary 情况

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

--注意：是当前时间

SQL10

请你找出所有非部门领导的员工emp_no

select e.emp_no
from employees e
left join dept_manager d
on e.emp_no = d.emp_no
where d.dept_no is  null

--左连接+is null
--左连接左边所有表都会有，右边没有的自动补为null

SQL11

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

select de.emp_no, dm.emp_no manager
from dept_emp de
join dept_manager dm
on de.dept_no = dm.dept_no
where de.emp_no <> dm.emp_no
and dm.to_date = '9999-01-01'
and de.to_date = '9999-01-01';

--可以先筛选出当前日期的manager和员工
--且员工ID与manager不一样

SQL12

获取所有部门中员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列

-- 错误：d.emp_no是非聚合字段，不能出现在SELECT。因为一个聚合字段(dept_no)对应多个非聚合字段(emp_no)，所以选择的时候，会随机选择非聚合字段中的任何一个，于是出错。

-- 聚合函数通过作用一组值而只返回一个单一值，因此，在SELECT语句中出现的字段要么为一个聚合函数的输入值，如COUNT(course)，要么为GROUP BY语句中指定的字段，要么是常数，否则会出错。

-- 简而言之：使用GROUP BY子句时，SELECT子句中只能有聚合键、聚合函数、常数

SELECT a.dept_no,b.emp_no,a.salary
FROM
    (SELECT dept_no,MAX(salary) AS salary
    FROM salaries
    INNER JOIN dept_emp
    ON dept_emp.emp_no=salaries.emp_no
    WHERE dept_emp.to_date = '9999-01-01'
    AND salaries.to_date='9999-01-01'
    GROUP BY dept_no) AS a                         --找出各部门最高薪资
INNER JOIN
    (SELECT dept_no,dept_emp.emp_no,salary
    FROM dept_emp
    INNER JOIN salaries
    ON dept_emp.emp_no=salaries.emp_no
    WHERE dept_emp.to_date = '9999-01-01'
    AND salaries.to_date='9999-01-01')AS b        --再找到各个员工的部门、薪资
ON a.salary=b.salary
AND a.dept_no=b.dept_no                            --连接查询后的俩表，筛选部门和最高薪资
ORDER BY dept_no

SQL15

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

按位与：https://blog.youkuaiyun.com/weixin_41013322/article/details/106146793

# 法一：使用%
SELECT *
FROM employees
WHERE emp_no % 2 = 1
AND last_name<>'Mary'
ORDER BY hire_date DESC;


# 法二：使用&
SELECT *
FROM employees
WHERE emp_no & 1
AND last_name<>'Mary'
ORDER BY hire_date DESC;


# 补充：不相等有三种表示方式：<>、!=、IS NOT
#   奇数 -- [expression]%2 = 1
    偶数 -- [expression]%2 = 0

SQL17

请你获取薪水第二多的员工的emp_no以及其对应的薪水salary

SELECT emp_no, salary
FROM salaries
ORDER BY salary DESC
LIMIT 1,1;

--但薪水第二的人可能会有多个，先确定第二高的工资，然后再把这个值当作条件查询

select emp_no, salary
from salaries
where salary = 
    (select distinct salary 
        from salaries 
        order by salary desc 
        limit 1,1
    );

SQL18

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成

select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary =              -- 第三步: 将第二高工资作为查询条件
    (
    select max(salary)        -- 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
    from salaries
    where salary <    
        (
        select max(salary)    -- 第一步: 查出原表最高工资
        from salaries
        where to_date = '9999-01-01'   
        )
    and to_date = '9999-01-01'
    )
and s.to_date = '9999-01-01'

SQL21

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

SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (    SELECT e.emp_no, s.salary
            FROM employees AS e
            LEFT JOIN salaries AS s
            ON e.emp_no=s.emp_no
            WHERE s.to_date='9999-01-01') AS sCurrent   --现在的工资
            
INNER JOIN (SELECT e.emp_no, s.salary
            FROM employees AS e
            LEFT JOIN salaries AS s
            ON e.emp_no=s.emp_no
            AND s.from_date=e.hire_date) AS sStart       --开始的工资
ON sCurrent.emp_no=sStart.emp_no
ORDER BY growth ASC;

SQL28

查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部

select c.name,count(fc.film_id)
FROM category c
JOIN film_category fc
ON c.category_id = fc.category_id
JOIN film f
ON f.film_id = fc.film_id
WHERE f.description LIKE '%robot%'
AND c.category_id IN (
        SELECT fc.category_id
        FROM film_category fc
        GROUP BY fc.category_id
        having count(*) >= 5)  --所含电影数目>5的分类id（跟robot无关）
group by c.category_id;

-- 不需要想太复杂了

SQL37

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

alter table actor add unique uniq_idx_firstname(first_name);
alter table actor add index idx_lastname(last_name);

    # 添加主键
   
    	
    ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
    // 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

    # 添加唯一索引

    	
    ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
    // 这条语句创建索引的值必须是唯一的。

    # 添加普通索引
 
    	
    ALTER TABLE tbl_name ADD INDEX index_name (col_list);
    // 添加普通索引，索引值可出现多次。

    # 添加全文索引
   
    	
    ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);
    // 该语句指定了索引为 FULLTEXT ，用于全文索引。

## PS: 附赠删除索引的语法：

	
DROP INDEX index_name ON tbl_name;
// 或者
ALTER TABLE tbl_name DROP INDEX index_name；
ALTER TABLE tbl_name DROP PRIMARY KEY

SQL41

构造一个触发器audit_log

CREATE TRIGGER audit_log 
AFTER INSERT ON employees_test
FOR EACH ROW
BEGIN
    INSERT INTO audit VALUES(new.id,new.name);
END

SQL44

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错。

UPDATE titles_test
SET emp_no = REPLACE(emp_no, 10001, 10005)
WHERE id = 5;

--语法：replace(object,search,replace)
--语义：把object对象中出现的的search全部替换成replace。

SQL61

对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name

# 方法一：利用窗口函数ROW_NUMBER() OVER(ORDER BY ) 

SELECT
    e.first_name
FROM employees e 
JOIN
    (
    SELECT
        first_name
        , ROW_NUMBER() OVER(ORDER BY first_name ASC) AS  r_num --该窗口函数排序为1，2，3，...
    FROM employees
    ) AS t
ON e.first_name = t.first_name
WHERE t.r_num % 2 = 1;


# 方法二：利用COUNT

-- 先排出号码   
    --计算行号的方法 : 有多少个小于等于e2.first_name的记录的个数就是e2.first_name的行号 
-- 然后再去奇数

SELECT first_name FROM  ( 
    SELECT e2.first_name,
            ( SELECT COUNT(*) 
              FROM employees AS e1 
              WHERE e1.first_name <= e2.first_name )  
              AS rownum 
    FROM employees AS e2 
    WHERE rownum % 2 =1 
)

--主要理解清楚 e1.first_name>=e2.first_name，同样都是字符串怎么比较呢？
--比如 select 'a' > 'b' 。可能会看不出来谁大谁小，怎么比较的呢？
--其实字符串内部是通过转换成 ascii 编码来进行大小比较的。
--如 我们 查询 select ascii('a') , asc('b') 会得到 65 与 66。所以 select 'a' > 'b' 返回 0

SQL63

请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列

SELECT 
    id,
    number,
    DENSE_RANK() OVER(ORDER BY number desc) t_rank
FROM 
    passing_number


--排序是按照number降序排的，不需要再次对t_rank排序

SQL65

写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序

select 
    date, 
    round(sum(type = "no_completed") / count(*), 3) as p
from email as t1
    join user as t2 on t1.send_id = t2.id
    join user as t3 on t1.receive_id = t3.id
where t2.is_blacklist = 0 and t3.is_blacklist = 0
group by date
order by date;

--发送的send_id 以及接受的 receive_id均是正常的

SQL67

请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序

SELECT 
    u.name u_n,
    c.name c_n,
    l.date 
FROM user u
JOIN login l
ON u.id = l.user_id
JOIN client c
ON c.id = l.client_id
WHERE (u.id, date)
IN (
    SELECT u.id, max(l.date)
    FROM user u, login l
    where u.id = l.user_id
    GROUP BY u.id)
ORDER BY u.name

--GROUP BY 之后对SELECT会有要求

--select 子句中只能存在以下三种元素：常数、聚合函数、group by子句指定列(聚合键)

SQL68

--（第一天登录的新用户并且第二天也登录的用户）/（总用户）
-- 为新登录用户的次日成功的留存率

--总用户

select count(distinct user_id) from login



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);

SQL69

请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序

# 方法一

--登录的当前日期=该用户所有登录日期的最小值
--计数神器————sum + case,不容易出错

select  
    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;


# 方法二

-- 1、先得到所有日期表select distinct date from login;

-- 2、然后左连接新用户首次登陆的日期表(select user_id, min(date) first_date from login group by user_id);

-- 3、归类统计日期出现的次数

select a.date, count(b.user_id) new
from (select distinct date 
      from login) a
left join (select user_id, min(date) first_date 
           from login 
           group by user_id) b 
on a.date=b.first_date 
group by a.date
order by a.date

SQL70

-- 分子：当前日期作为前一天有该用户的登录记录 并且第一次登录（12号作为前一天有这个人，说明13号有这个人）

-- 分母：当前日期新用户的特征是  当前日期=该用户所有登录日期的最小值

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;

SQL71

# 两种方法：

    -- 使用窗口函数SUM，将日期作为“窗口”计算累加刷题数量
    -- 使用自联结 

-- 窗口函数

SELECT name AS u_n,date,
SUM(number) OVER (PARTITION BY user_id ORDER BY date) AS ps_num
FROM passing_number AS p
INNER JOIN user
ON user.id=p.user_id
GROUP BY date,u_n
ORDER BY date,name


-- 使用自联结

SELECT name AS u_n,date,ps_num
FROM (
    SELECT p1.user_id,p1.date,SUM(p2.number)AS ps_num
    FROM  passing_number AS p1,passing_number AS p2
    WHERE p1.date>=p2.date
    AND p1.user_id=p2.user_id
    GROUP BY p1.date,p1.user_id
    ) AS p
INNER JOIN user
ON user.id=p.user_id
GROUP BY date,u_n
ORDER BY date,name

SQL74

请你找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序

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;


 --   直接使用窗口函数进行排名，然后筛选即可

 --   先按照language的name升序排序
 --   再按照积分降序排序
 --   最后按照grade的id升序排序

SQL75

请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序

-- FLOOR(x) 返回小于或等于 x 的最大整数.

-- CEIL(x) 返回大于或等于 x 的最小整数

-- IF(expr,v1,v2) 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2

-- sum(1)跟count(*)是一个意思都是对获取总行数的意思

SELECT job, 
    floor(( count(*) + 1 )/ 2 ) AS "start", 
    floor(( count(*) + 2 )/ 2 ) AS 'end' 
FROM grade 
GROUP BY job 
ORDER BY job

SQL88

请你写SQL帮忙查询一下，如果只有1个中位数，输出1个，如果有2个中位数，按grade升序输出

 # 首先我们需要知道:当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数

--比如:

-- A A B B C C D D 
-- 1 2 3  4  5 6  7 8
-- 8 7 6  5  4  3 2 1

# 那么上面的4，5以及5，4就是中位数，如果是奇数的话，就只有1个 

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;

SQL90

请你写一个SQL查找积分增加最高的用户的id(可能有多个)，名字，以及他的总积分是多少，查询结果按照id升序排序

# 最大的grade_num     t2
SELECT SUM(grade_num) grade_num
FROM grade_info
GROUP BY user_id
ORDER BY grade_num DESC
LIMIT 1

# 各用户得分表的总和 t1
SELECT user_id,SUM(grade_num) grade_num
FROM grade_info
GROUP BY user_id


# 对表一进行分类，选出grade_num最大的user_id
SELECT user_id,u.name,grade_num
FROM (
	SELECT user_id,SUM(grade_num) grade_num
	FROM grade_info
	GROUP BY user_id
) t1
JOIN USER u 
ON t1.user_id= u.id
WHERE grade_num = (
			SELECT SUM(grade_num) grade_num
			FROM grade_info
			GROUP BY user_id
			ORDER BY grade_num DESC
			LIMIT 1
)
ORDER BY user_id

SQL91

请你写一个SQL查找积分增加最高的用户的id，名字，以及他的总积分是多少(可能有多个)，查询结果按照id升序排序

# 各用户实际得分
SELECT user_id,
	CASE TYPE
	WHEN 'add' THEN grade_num
	WHEN 'reduce' THEN -grade_num
	END g_sum
FROM grade_info

# 各用户实际得分汇总表 t
SELECT user_id,SUM(g_sum) grade_num
FROM  (SELECT user_id,
	CASE TYPE
	WHEN 'add' THEN grade_num
	WHEN 'reduce' THEN -grade_num
	END g_sum
	FROM grade_info) t
GROUP BY user_id

# 汇总后 最高得分
SELECT SUM(g_sum) grade_num
FROM  (SELECT user_id,
	CASE TYPE
	WHEN 'add' THEN grade_num
	WHEN 'reduce' THEN -grade_num
	END g_sum
	FROM grade_info) t
GROUP BY user_id
ORDER BY grade_num DESC
LIMIT 1

# 
SELECT u.id, u.name,grade_num
FROM USER u
JOIN (
	SELECT user_id,SUM(g_sum) grade_num
	FROM  (SELECT user_id,
		CASE TYPE
		WHEN 'add' THEN grade_num
		WHEN 'reduce' THEN -grade_num
		END g_sum
		FROM grade_info) t
	GROUP BY user_id
) t1
ON u.id = t1.user_id
WHERE grade_num = (
		SELECT SUM(g_sum) grade_num
		FROM  (SELECT user_id,
			CASE TYPE
			WHEN 'add' THEN grade_num
			WHEN 'reduce' THEN -grade_num
			END g_sum
			FROM grade_info) t
		GROUP BY user_id
		ORDER BY grade_num DESC
		LIMIT 1
)
ORDER BY u.id