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

加我一个吧,我也要刷sql.

我也刚开始刷sql，求队友互相监督，可以加我katezzzhao

iilla 发表于 2019-7-5 10:36
我也刚开始刷sql，求队友互相监督，可以加我katezzzhao

已加！紫薯紫薯

viviantx 发表于 2019-7-1 06:55
加我一个吧,我也要刷sql.

刷起来！一起盖楼呀～

181. Employees Earning More Than Their Managers
SELECT e1.name as employee
FROM employee e1
WHERE e1.managerid is not NULL and e1.salary > (SELECT e2.salary FROM employee e2 WHERE e1.managerid = e2.id)

/*不用subquery可大幅提速，耗时比解1少了一半*/
SELECT e1.name as employee
FROM employee e1, employee e2
WHERE e1.managerid is not NULL and e1.managerid = e2.id AND e1.salary > e2.salary

182. Duplicate Emails
SELECT email
FROM person
GROUP BY Email
HAVING COUNT(*) > 1

SELECT DISTINCT email
FROM person p1
JOIN person p2
ON p1.email = p2.email AND p1.id != p2.id

196. Delete Duplicate Emails
DELETE
FROM person
WHERE id NOT IN (SELECT MIN(id) FROM person GROUP BY email) temp)

DELETE p1
FROM person p1, person p2
WHERE p1.email = p2.email AND p1.id > p2.id

183. Customers Who Never Order
/*注意执行顺序：FROM (JOIN ON) -> WHERE 所以不需要temp table，直接选join后where即可*/
SELECT c.name as customers
FROM customers c
LEFT JOIN orders o
ON c.id = o.customerid
WHERE temp.customerid IS NULL

SELECT name as customers
FROM customers
WHERE id NOT IN (SELECT customerid FROM orders)
/*NOT EXISTS faster than NOT IN*/
SELECT name as customers
FROM customers c
WHERE NOT EXISTS (SELECT * FROM orders o WHERE c.id = o.customerid)

197. Rising Temperatures
SELECT w2.id
FROM weather w1, weather w2
WHERE DATEDIFF(w2.recorddate, w1.recorddate) = 1 AND w1.temperature < w2.temperature

/*DATEDIFF语句也可用TO_DAYS(w2.date) - TO_DAYS(w1.date) = 1代替，TO_DAYS是从0年以来的天数，注意0000-00-00被认为是无效日期所以会返回null，0000-01-01会返回1*/

262. Trips and Users
cnt(trips cancelled by unbanned users, including client & driver)/cnt(all trips)
where day between ()
SELECT request_at as day, ROUND(SUM(CASE WHEN status !=  ‘completed’ THEN 1 ELSE 0 END) / COUNT(*), 2) as cancellation rate
FROM trips t JOIN (SELECT users_id FROM users u WHERE role = ‘client’ OR role = ‘driver’) temp
ON t.client_id = temp.users_id OR t.driver_id = temp.users_id
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at
ORDER BY request_at