# 查询出每个成员的每次工资变化情况
from salaries s where s.emp_no &＃61; de.emp_no))
# 查询出部门中的每个成员
from dept_emp de where de.dept_no &＃61; d.dept_no) as sum
from departments d;
5.牛客网SQL第23题(非窗口写法)
#注意去重
select a.emp_no &＃xff0c;a.salary&＃xff0c;
count(distinct b.salary)
from salaries as a,salaries as b
where a.to_date &＃61; &＃39;9999-01-01&＃39;
and b.to_date &＃61;&＃39;9999-01-01&＃39;
and a.salary<&＃61; 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 &＃61;salaries.emp_no
and dept_emp.to_date&＃61;&＃39;9999-01-01&＃39;
and salaries.to_date&＃61;&＃39;9999-01-01&＃39;) as t1
join
(select
dept_no,salary,dept_manager.emp_no
from
dept_manager
join
salaries
on dept_manager.emp_no &＃61;salaries.emp_no
and dept_manager.to_date&＃61;&＃39;9999-01-01&＃39;
and salaries.to_date&＃61;&＃39;9999-01-01&＃39;
) as t2
on t1.dept_no &＃61; t2.dept_no
and t1.salary > t2.salary
7.牛客网SQL第29题(注意事项)
最后一句若写成 ON f.film_id &＃61; fc.film_id AND fc.category_id IS NULL&＃xff0c;则意义变
成左连接两表 film_id 相同的记录&＃xff0c;且 film_category 原表中的 fc.category 的值为
null。显然&＃xff0c;原表中的 fc.category 的值恒不为 null&＃xff0c;因此(f.film_id &＃61; fc.film_id
AND fc.category_id IS NULL)恒为 FALSE&＃xff0c;左连接后则只会显示 film 表的数
据&＃xff0c;而 film_category 表的数据全显示为 null
8.牛客网SQL第35题(mysql语法补充)
加入数据存在忽略.insert
insert IGNORE into actor values (&＃39;3&＃39;, &＃39;WD&＃39;, &＃39;GUINESS&＃39;, &＃39;2006-02-15 12:34:33&＃39;);
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( &＃39;10,A,B&＃39;) - length(REPLACE( &＃39;10,A,B&＃39;,&＃39;,&＃39;,&＃39;&＃39;));
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)&＃xff0c;其中X是要连接的字段&＃xff0c;Y是连接时用的符号&＃xff0c;可省略&＃xff0c;默认为逗号。此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组&＃xff0c;将每个分组中不同的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&＃61;&＃39;&＃39;)来求和,忽视了casewhen的用法)
select
t1.date,
sum(case t1.type when&＃39;completed&＃39; 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 &＃61;1)
and receive_id not in (
select
id
from
user
where is_blacklist &＃61;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),"&＃43;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 &＃61; b.user_id and a.rank &＃61; 1 and b.rank &＃61; 2
where b.date &＃61; date(a.date,"&＃43;1 day");
16.牛客网SQL69题(每日新登录人数)
(sum case when 的用法赋值)
select a.date,
sum(case when t_rank&＃61;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 &＃61; 1) t2
group by t2.date) t3
on t4.date &＃61; 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 &＃61; b.user_id
AND b.date &＃61; date(a.date, &＃39;&＃43;1 day&＃39;)
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 &＃61; l.id
WHERE score >&＃61; (SELECT IFNULL(MAX(score),0) FROM grade g2
WHERE g2.language_id &＃61; g.language_id
AND score <(SELECT MAX(score) FROM grade g3
WHERE g3.language_id &＃61; g.language_id))
ORDER BY l.name ASC , g.score DESC
19.牛客网75题(使用(count(*)&＃43;1) /2来避免奇偶性小数的出现)
SELECT
job,
CASE WHEN total%2&＃61;0 THEN total/2 ELSE (total&＃43;1)/2 END AS &＃96;start&＃96;,
CASE WHEN total%2&＃61;0 THEN total/2&＃43;1 ELSE (total&＃43;1)/2 END AS &＃96;end&＃96;
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&＃61;1 and rn &＃61; dn) -- 若排序总数为奇数&＃xff0c;则中位数的正序位置与倒序位置相等
or (cnt%2&＃61;0 and abs(rn-dn)&＃61;1) --若排序总数为偶数&＃xff0c;则中位数的正序位置与倒序位置正好差1
order by id
京公网安备 11010802041100号