SQL刷题记录 求战友互相督促交流

580. Count Student Number in Departments
select dept_name, student_number
from department d join
(select dept_id, ifnull(count(distinct student_id) over(partition by dept_id), 0) as student_number from student) num
on d.dept_id = num.dept_id
order by student_number desc, dept_name

select dept_name, student_number
from department d join
(select dept_id, ifnull(count(distinct student_id), 0) as student_number from student group by dept_id) num
on d.dept_id = num.dept_id
order by student_number desc, dept_name

/*TODO: 即使不ifnull，只要做了left join就可以正确输出student_number = 0的情况，为什么？？*/
select dept_name, ifnull(count(distinct student_id), 0) as student_number
from department d join student
on d.dept_id = student.dept_id
group by dept_name
order by student_number desc, dept_name

584. Find Customer Referee
select name
from customer
where referee_id is null or referee_id != 2

585. Investments in 2016
select sum(round(tiv_2016, 2)) as tiv_2016
from insurance a
-baidu 1point3acreswhere (select count(1) from insurance b where a.lat = b.lat and a.lon = b.lon) = 1 and (select count(1) from insurance c where a.tiv_2015 = b.tiv_2015) > 1

/*TODO: 结果不正确。按理说不是需要同时满足same tiv_2015和不同city这两个条件么，一起and做筛选为何不可以？*/
select sum(round(tiv_2016, 2)) as tiv_2016
from insurance
where pid in
(select i.pid
from insurance i
where exists
(select 1 from insurance i1 where i.pid != i1.pid and i.tiv_2015 = i1.tiv_2015 and i.lat != i1.lat and i.lon != i1.lon))

/*修改为以下代码就对了*/
select sum(round(tiv_2016, 2)) as tiv_2016
from insurance a
where exists
(select 1 from insurance b where a.pid != b.pid and a.tiv_2015 = b.tiv_2015)
and not exists
(select 1 from insurance c where a.pid != c.pid and a.lat = c.lat and a.lon = c.lon)

马一个，楼主有心了！加米！

may8889 发表于 2019-9-13 23:33
马一个，楼主有心了！加米！

haha thanks! 真的很需要米。。。quq

Hackerrank weather observation station 20 求中位数

SELECT ROUND(lat_n,4) AS median
FROM station s
WHERE (SELECT COUNT(lat_n) FROM STATION WHERE lat_n > s.lat_n) = (SELECT COUNT(lat_n) FROM STATION WHERE lat_n < s.lat_n)   

几个细节：
1.  >, not >=
2. count lat_n，not count * group by lat_n
3. 在两个subquery内比较，所以只alias for最终呈现的table即可

hackerrank the report

SELECT CASE WHEN g.grade > 7 THEN s.name ELSE NULL END AS name, g.grade, s.marks
FROM students s, grades g
WHERE s.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY grade DESC, COALESCE(name, marks)
/*trick在于如何实现当grade>7时按name排序，当grade>7时按marks排序。原本我用了where+union，但其实利用null的条件，coalesce/ifnull即可解决*/

601. Human Traffic of Stadium
select s1.*
from stadium s1, stadium s2, stadium s3
where s1.people >= 100 and s2.people >= 100 and s3.people >= 100
and ((s1.id = s2.id - 1 and s1.id = s3.id - 2)
or (s1.id = s2.id + 1 and s1.id = s3.id - 1)
or (s1.id = s2.id + 2 and s1.id = s3.id + 1))
group by s1.id

/*This one beats 100% MS SQL online submissions 骄傲地发了个discussion嘻嘻*/
select id, visit_date, people
from
(select *,
lead(people) over(order by id) as after1,
lag(people) over(order by id) as before1,
lead(people, 2) over(order by id) as after2,
lag(people, 2) over(order by id) as before2
from stadium) temp
where people >= 100 and (
(before1 >= 100 and after1 >= 100)
or (after1 >= 100 and after2 >= 100)
or (before1 >= 100 and before2 >= 100))

我才开始刷题 搜面经题感觉像天书一样 学的跟考的也差太多了