注册    登录
关注
Py学习  »  DATABASE

2020-11-19-Mysql(牛客网SQL题要点详解)

隐约喜欢萌萌哒 • 3 年前 • 319 次点击  

1.distinct去重函数 

select
title as 'Assistant Engineer',count(distinct emp_no) as t
from
titles
group by title
having t>=2

2.limit的用法 limit 1,2 从位移1截取取2个 

//取第二,存在多个人排第二的情况
select emp_no, salary
from salaries
where salary = (
    select salary from salaries 
    group by salary
    order by salary desc limit 1,1
) 
and to_date = '9999-01-01'

3.牛客网SQL第18题 

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'

(不支持下面的写法,select中存在非groupby 字段,) 

select e.emp_no, max(s.salary),e.last_name,e.first_name
from salaries as s
inner join employees as e
on e.emp_no = s.emp_no
where s.to_date='9999-01-01'
and s.salary < (select max(salary) from salaries where salaries.to_date = '9999-01-01');

4.牛客网SQL第22题(有意义的写法) 

select 
    d.dept_no,
    d.dept_name,
    (select 
         sum((select 
                 sum((select 
                     case
                         # 记录数为0说明是第一条记录（原来的答案里有这一条，测试后发现加上去答案和预期不符）
                         # when count(*) = 0 then 0
                         # 最近一次工资变化比当前工资低判定为涨工资
                         when s0.salary < s.salary then 1
                         # 其他情况判定为不是涨工资
                         else 0 end
                     # 查询最近一次工资变化情况
                     from salaries s0 where s0.emp_no = s.emp_no and s0.to_date < s.to_date order by s0.to_date desc limit 1))
             # 查询出每个成员的每次工资变化情况
             from salaries s where s.emp_no = de.emp_no))
     # 查询出部门中的每个成员
     from dept_emp de where de.dept_no = d.dept_no) as sum 
from departments d;

5.牛客网SQL第23题(非窗口写法) 

#注意去重
select  a.emp_no ，a.salary，
count（distinct b.salary）
from salaries as a,salaries as b
where a.to_date = '9999-01-01' 
and b.to_date ='9999-01-01'
and a.salary<= b.salary
order by salary desc;

6.牛客网SQL第25题(有点麻烦) 

select
t1.emp_no as emp_no,t2.emp_no as manager_no,
t1.salary as emp_salary,t2.salary as manager_salary
from
(
select
dept_emp.emp_no,dept_no,salary
from
dept_emp
join 
salaries
on  dept_emp.emp_no =salaries.emp_no
and dept_emp.to_date='9999-01-01'
and salaries.to_date='9999-01-01') as t1
join 
(select
dept_no,salary,dept_manager.emp_no
from
dept_manager
join 
salaries
on dept_manager.emp_no =salaries.emp_no
and dept_manager.to_date='9999-01-01'
and salaries.to_date='9999-01-01'
) as t2
on t1.dept_no = t2.dept_no
and t1.salary > t2.salary

7.牛客网SQL第29题(注意事项) 

最后一句若写成 ON f.film_id = fc.film_id AND fc.category_id IS NULL，则意义变

成左连接两表 film_id 相同的记录，且 film_category 原表中的 fc.category 的值为 

null。显然，原表中的 fc.category 的值恒不为 null，因此（f.film_id = fc.film_id 

AND fc.category_id IS NULL）恒为 FALSE，左连接后则只会显示 film 表的数

据，而 film_category 表的数据全显示为 null

8.牛客网SQL第35题(mysql语法补充) 

加入数据存在忽略.insert 
insert  IGNORE into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');

9.牛客网SQL 第42题(犯了一个逻辑错误)

(我在group by 后面添加了having count()>1 导致min(id)中没有此项id not in 故删除了干这个数据)
(当然正向思维找出需要删除的id也可以having count() > 1 找出1 2 3 ) 

delete from titles_test
where  titles_test.id not in 
(
select 
min(id)
from
titles_test
group by emp_no
)

10.牛客网SQL第51题(使用replace方法)

length( '10,A,B') - length(REPLACE( '10,A,B',',',''));

11.牛客网第52题(使用substr,第二个参数客为负数,从后面截取) 

select 
first_name
from
employees
order by  substr(first_name,-2,2)

12.牛客网第53题(group_concat(X,Y))

聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y） 

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

13.牛客网第55题,limit()实现分页 

select *
from employees
limit 5, 5;

14.牛客网SQL第65题(case when 求和)

(我本来是count(tpye='')来求和,忽视了casewhen的用法) 

select 
t1.date,
sum(case t1.type when'completed' then 0 else 1 end) *1.0/count(t1.type)
from 
(
select
*
from
email
where 
send_id not in
(
select 
id
from
user
where is_blacklist =1)
and  receive_id not in  (
select 
id
from
user
where is_blacklist =1)) t1
group by t1.date

15.牛客网SQL68题(次留存) 

select round(1.0*count(distinct user_id)/(select count(distinct user_id) from login),3)
    from login 
        where (user_id, date) in (
            select user_id, date(min(login.date),"+1 day")
                from login 
                group by user_id
        );

select round(1.0*count(distinct a.user_id)/(select count(distinct user_id) from login),3)
        from (
            select *, dense_rank() over (partition by user_id order by date) as rank
                from login
        ) as a 
        join (
            select *, dense_rank() over (partition by user_id order by date) as rank
                from login
        ) as b on a.user_id = b.user_id and a.rank = 1 and b.rank = 2 
        where b.date = date(a.date,"+1 day");

16.牛客网SQL69题(每日新登录人数)

(sum case when 的用法赋值) 

select a.date,
sum(case when t_rank=1 then 1 else 0 end) new
from 
(select date, row_number() over(partition by user_id order by date) t_rank
from login) a
group by date;

(我的写法.......没想到使用case when) 

select 
t4.date,ifnull(t3.new,0)
from
(select 
date
from
login
group by  date) t4
left join 
(select 
t2.date,count(user_id) as new
from 
(
select 
t1.user_id,t1.date
from
(
select
user_id,
date,
row_number() over(partition by user_id order by date) as number
from
login) t1
where number = 1) t2
group by t2.date) t3
on t4.date = t3.date

17.牛客网SQL70题(每个日期新用户的次日留存率) 

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;

18.牛客网SQL74题(非窗口函数写法取最大然后取第二)

(不用排序排序函数的写法,先找到最大的,接着找到第二大的) 

SELECT
    g.id,
    l.name,
    g.score
FROM
    grade g JOIN LANGUAGE l
    ON g.language_id = l.id
WHERE score >= (SELECT IFNULL(MAX(score),0) FROM grade g2 
                WHERE g2.language_id = g.language_id
               AND score < (SELECT MAX(score) FROM grade g3 
                                 WHERE g3.language_id = g.language_id))
ORDER BY l.name ASC , g.score DESC

19.牛客网75题(使用(count(*)+1) /2来避免奇偶性小数的出现) 

SELECT 
job,
CASE WHEN total%2=0 THEN total/2 ELSE (total+1)/2 END AS `start`,
CASE WHEN total%2=0 THEN total/2+1 ELSE (total+1)/2 END AS `end`
FROM
(SELECT job,COUNT(1) AS total
FROM grade
GROUP BY job)t;

20.牛客网76题(使用abs方法) 

select id,
job,
score,
dn rank
from
(select id,
job,
score,
row_number() over(partition by job order by score) rn,--正序位置
row_number() over(partition by job order by score desc) dn,--倒序位置
count() over(partition by job) cnt--参与排序总数
from grade)
where (cnt%2=1 and rn = dn) -- 若排序总数为奇数，则中位数的正序位置与倒序位置相等
or (cnt%2=0 and abs(rn-dn)=1) --若排序总数为偶数，则中位数的正序位置与倒序位置正好差1
order by id
Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/99315
Reply
 
319 次点击  
登录后回复
关于   移动版   ·   三行代码   ·   今天看啥   ·   Code   ·   link之家   ·   卧龙搜索   ·   藏经阁   ·   小百科
Py学习 - 专注于Python技术发展的社区(原Django社区)
沪ICP备11025650号