sql学习，刷题6

2021-08-10 23:27:18

牛客每次考试完，都会有一个成绩表(grade)，如下:

第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了B语言岗位并且考了9999分
请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下

解释：
第1行表示C++岗位的中位数位置上的为用户id为2，分数为10000，在C++岗位里面排名是第2
第2，3行表示Java岗位的中位数位置上的为用户id为4,5，分数为12000,13000，在Java岗位里面排名是第2,1
第4行表示B语言岗位的中位数位置上的为用户id为7，分数为11000，在前端岗位里面排名是第2
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round，sqlite不支持floor函数，支持cast(x as integer) 函数，不支持if函数，支持case when …then …else …end函数，sqlite不支持自定义变量)
【妙解】查询每组中位数位置上的记录
#中位数本身就是先按自然数排名，再取数。排序函数选用row_number
方法一（妙解）
用一条规则统一奇数个数时和偶数个数时的中位数位置。无论奇偶，中位数的位置距离（个数+1）/2 小于1，不信你随便写个试试。

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
order by id;

方法二（常规思路）：
#对grade表添加组内排名列作为表1，各组中位数的表（也就是上题的查询）作为表2，
#用相同组（job）关联表1表2，用where筛出表1中的组内排名等于表2中组内start位置或end位置。

⚠️把倒数第二行的&nbs***bsp;替换成为or，否则报错

select t1.id
        ,t1.job
        ,t1.score
        ,t1.s_rank
from 
(select id,job,score
        ,(row_number()over(partition by job order by score desc))as s_rank
        from grade)t1
join 
(select job
        ,case when count(score)%2=0 then ceiling(count(score)/2) else ceiling(count(score)/2)
        end as start1
        ,case when count(score)%2=0 then ceiling(count(score)/2+1) else ceiling(count(score)/2)
        end as end1
from grade
group by job)t2
on t1.job=t2.job 
where t1.s_rank=t2.start1&nbs***bsp;t1.s_rank=t2.end1
order by t1.id;

假设云音乐数据库里面现在有几张如下简化的数据表:
关注follow表,第一列是关注人的id,第二列是被关注人的id,这2列的id组成主键

这张表的第一行代表着用户id为1的关注着id为2的用户
这张表的第二行代表着用户id为1的关注着id为4的用户
这张表的第三行代表着用户id为2的关注着id为3的用户
个人的喜欢的音乐music_likes表,第一列是用户id,第二列是喜欢的音乐id,这2列的id组成主键

这张表的第一行代表着用户id为1的喜欢music_id为17的音乐
…
这张表的第五行代表着用户id为4的喜欢music_id为17的音乐
音乐music表，第一列是音乐id，第二列是音乐name,id是主键

请你编写一个SQL，查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。
不要推荐该用户已经喜欢的音乐，并且按music的id升序排列。你返回的结果中不应当包含重复项
上面的查询结果如下:

第一步：用户1关注了哪些人
select follower_id
from follow
where user_id=1
第二步：用户1喜欢的音乐
select music_id
from music_likes
where user_id=1
第三步：关注的那些人喜欢的音乐
select music_id
from music_likes
where user_id in (
    select follower_id
    from follow
    where user_id=1
)
第四步：除了用户1喜欢的音乐外，关注的那些人喜欢的其他音乐
select music_id
from music_likes
where user_id in (
    select follower_id
    from follow
    where user_id=1
) and music_id not in(
select music_id
from music_likes
where user_id=1
)
第五步：与音乐表联结
select distinct m.music_name
from music m join 
(
    select music_id
    from music_likes
    where user_id in (
        select follower_id
        from follow
        where user_id=1
    ) and music_id not in(
    select music_id
    from music_likes
    where user_id=1
)
)a
on a.music_id=m.id
order by m.id

select music_name
from music
where id IN (select distinct music_id
from music_likes
where user_id in (select follower_id
from follow
where user_id = 1))
and id not in (select music_id
from music_likes
where user_id = 1)
order by id;

有一个员工表dept_emp简况如下:

有一个薪水表salaries简况如下:

获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列，以上例子输出如下:
（注意: Mysql与Sqlite select 非聚合列的结果可能不一样

此题思路如下：
1、先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no；
2、选取每个员工当前的工资水平，用d.to_date = ‘9999-01-01’ AND s.to_date = '9999-01-01’作条件限制，因为此表中每条最新记录的 to_date 都用 9999-01-01 表示；
3、用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；
4、将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出。

SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no 
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
GROUP BY d.dept_no

割线：若存在多条最大记录----------------------------------------
有同学提出疑问，如果存在多条最大记录怎么办？而 MAX 函数根据不同数据库只选择最前一条或最后一条最大记录，其余记录均被忽略。此时解法如下：
1、创建两张表，一张为maxsalary，用于存放当前每个部门薪水的最大值；另一张为currentsalary，用于存放当前每个部门所有员工的编号和薪水；
2、限定条件为两张表的 dept_no 和 salary 相等，这样就可以找出当前每个部门所有薪水等于最大值的员工的相关信息了；
3、最后记得根据 currentsalary.dept_no 升序排列，输出与参考答案相同的记录表。
4、以下代码虽然很长，仔细一看都是基于上面的基础解法变化而来的，中心思想就是绕开 MAX 的特性限制，运用比较的方法选出多个相同的最大值。

SELECT currentsalary.dept_no, currentsalary.emp_no, currentsalary.salary AS salary
FROM 
//创建maxsalary表用于存放当前每个部门薪水的最大值
(SELECT d.dept_no, MAX(s.salary) AS salary
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no 
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
GROUP BY d.dept_no) AS maxsalary, 
//创建currentsalary表用于存放当前每个部门所有员工的编号和薪水
(SELECT d.dept_no, s.emp_no, s.salary 
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no 
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
) AS currentsalary
//限定条件为两表的dept_no和salary均相等
WHERE currentsalary.dept_no = maxsalary.dept_no
AND currentsalary.salary = maxsalary.salary
//最后以currentsalary.dept_no排序输出符合要求的记录表
ORDER BY currentsalary.dept_no

码农公寓

相关文章