sql学习，刷题5

最新推荐文章于 2023-07-23 15:38:14 发布

xxxxrr

最新推荐文章于 2023-07-23 15:38:14 发布

阅读量161

点赞数

文章标签： sql sqlite 数据库

本文链接：https://blog.csdn.net/xxxxrr/article/details/122701674

版权

有一个员工表employees简况如下:
在这里插入图片描述
有一个薪水表salaries简况如下:

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:
（温馨提示:sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。如果使用非group by的列名，sqlite的结果和mysql 可能不一样)
在这里插入图片描述

解法1 max 任意相同工资人数
select e.emp_no,s.salary,e.last_name,e.first_name
from
employees e
join 
salaries s on e.emp_no=s.emp_no 
and  s.to_date='9999-01-01'
and 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'
                )

第二种 通用型可以求任意第几高，并且可以求多个形同工资
select e.emp_no,s.salary,e.last_name,e.first_name
from
employees e
join 
salaries s on e.emp_no=s.emp_no 
and  s.to_date='9999-01-01'
and s.salary = 
(
     select s1.salary
     from 
     salaries s1
     join
     salaries s2 on s1.salary<=s2.salary 
     and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
     group by s1.salary
     having count(distinct s2.salary)=2
 )

TM小哥和FH小妹在牛客大学若干年后成立了牛客SQL班，班的每个人的综合成绩用A,B,C,D,E表示，90分以上都是A，80_{90分都是B，70}80分为C，60~70为D，E为60分以下
假设每个名次最多1个人，比如有2个A，那么必定有1个A是第1名，有1个A是第2名(综合成绩同分也会按照某一门的成绩分先后)。
每次SQL考试完之后，老师会将班级成绩表展示给同学看。
现在有班级成绩表(class_grade)如下
在这里插入图片描述
第1行表示成绩为A的学生有2个
…
最后1行表示成绩为D的学生有2个
老师想知道学生们综合成绩的中位数是什么档位，请你写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个
再比如
A2个，B3个，C5个，D2个，
正序2，5，10，12
倒序12，10，7，2
正序和12，大于等于6的为C，D，
逆序和12，大于等于6的为A，B，C，所以最后中位数为C
根据(一)https://blog.nowcoder.net/n/60f8ed8d360c4307a8875349caf22b77 我们知道求正序和的写法，
求逆序和其实就是 sum(a) over (order by b desc) 就行了，多加了一个desc，那么我们可以写出如下代码:

 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;

得到中间表的结果如下:
在这里插入图片描述
将 a>=total的一半并且 b>=total的一半列出，就得到了最后需要的结果:

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) t1
where a >= total/2 and b >=total/2
order by grade;

有一个部门表departments简况如下:
在这里插入图片描述
有一个，部门员工关系表dept_emp简况如下:

有一个职称表titles简况如下:

汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序，dept_no一样的再按title升序排序
在这里插入图片描述
本题的关键在于用 GROUP BY 同时对 de.dept_no 和 t.title
进行分组，具体思路如下：

1、先用 INNER JOIN 连接 dept_emp 与 salaries，根据测试数据添加限定条件 de.to_date =
‘9999-01-01’ AND t.to_date = ‘9999-01-01’，即当前员工的当前头衔
2、再用 INNER JOIN 连接departments，限定条件为 de.dept_no =
dp.dept_no，即部门编号相同
3、最后用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组，用 COUNT(t.title)
统计相同部门下相同头衔的员工个数

SELECT de.dept_no,dp.dept_name,t.title,count(t.title) count
from titles t inner join dept_emp de
on t.emp_no = de.emp_no 
inner join departments dp
on de.dept_no = dp.dept_no
group by de.dept_no,t.title
order by de.dept_no,t.title

有很多同学在牛客购买课程来学习，购买会产生订单存到数据库里。

有一个订单信息表(order_info)，简况如下
在这里插入图片描述
第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。
第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单，状态为购买成功。
最后1行表示user_id为557336的用户在2025-10-26的时候使用了client_id为1的客户端下了Python课程的订单，状态为购买成功。
请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序，以上例子查询结果如下:
在这里插入图片描述
解析:

id为4，6的订单满足以上条件，输出57，id为4的订单为第一次购买成功，输出first_buy_date为2025-10-23，id为6的订单为第二次购买，输出second_buy_date为2025-10-24，总共成功购买了2次;

id为5，7，8的订单满足以上条件，输出557336，id为5的订单为第一次购买成功，输出first_buy_date为2025-10-23，id为7的订单为第二次购买，输出second_buy_date为2025-10-25，总共成功购买了3次;

可考虑使用lead函数，该函数作用是：获取某顺序字段当前记录的下一条记录；具体用法是：lead(date,1,0)over(partition by user_id order by date) as second_date 这样便可以得到第二次购买日期字段

select user_id,min(date) as first_buy_date,
second_buy_date,
count(*)as cnt 
from
(select *,count(*)over(partition by user_id)as cnt,
 lead(date,1,0)over(partition by user_id order by date) as second_buy_date
 from order_info 
 where product_name in('Java','C++','Python') 
  and status='completed'
  and date>'2025-10-15')t
where t.cnt>1 group by user_id order by user_id

select user_id
      ,min(case when r = 1 then date end) as first_buy_date
      ,min(case when r = 2 then date end) as second_buy_date
      ,count(1)                           as cnt
from ( 
    select *
          ,rank() over(partition by user_id order by date) as r
    from order_info
    where 1=1
    and status = 'completed'
    and date > '2025-10-15'
    and product_name in ('C++', 'Python', 'Java'))t
group by user_id
having count(1) >= 2

对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name
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));
如，输入为：
INSERT INTO employees VALUES(10001,‘1953-09-02’,‘Georgi’,‘Facello’,‘M’,‘1986-06-26’);
INSERT INTO employees VALUES(10002,‘1964-06-02’,‘Bezalel’,‘Simmel’,‘F’,‘1985-11-21’);
INSERT INTO employees VALUES(10005,‘1955-01-21’,‘Kyoichi’,‘Maliniak’,‘M’,‘1989-09-12’);
INSERT INTO employees VALUES(10006,‘1953-04-20’,‘Anneke’,‘Preusig’,‘F’,‘1989-06-02’);
输出格式:
在这里插入图片描述
首先题目的叙述有问题，导致理解有误，输出的数据与参考答案不同。先给出正确的题目叙述：【对于employees表，在对first_name进行排名后，选出奇数排名对应的first_name】。
1、本题用到了三层 SELECT 查询，为了便于理解，采用缩进方式分层，且最外层对应e1，最内层对应e3；
2、在e3层中，采用 COUNT() 函数对 e2.first_name 进行排名标号，即在给定 e2.first_name的情况下，不大于 e2.first_name 的 e3.first_name 的个数有多少，该个数刚好与 e2.first_name 的排名标号匹配，且将该值命名为 rowid；
/注意：排名标号后并未排序，即[Bob, Carter, Amy]的排名是[2,3,1]，选取奇数排名后输出[Carter, Amy]，所以可见参考答案中的first_name并未按字母大小排序/
3、在e1层中，直接在限定条件 e1.rowid % 2 = 1 下，代表奇数行的 rowid，选取对应的 e1.first_name；
4、e2层则相当于连接e1层（选取表示层）与e3层（标号层）的桥梁

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

xxxxrr

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sql学习，刷题5

有一个员工表employees简况如下:有一个薪水表salaries简况如下:请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:（温馨提示:sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。如果使用非group by的列名，sqlite的结果和mysql 可能不一样)解法
复制链接

扫一扫