sql刷题笔记

1.这种情况下，id%2 = 1写在description<>"boring"前面效率更高，反之效率低

select * from cinema where id%2 = 1 and description<>"boring" order by rating desc

2.join（不包括空值），left join （包括空值）

select name, bonus
from Employee left join Bonus
on Employee.EmpId = Bonus.EmpId
where bonus is null or bonus < 1000

3.

262. 行程和用户https://leetcode.cn/problems/trips-and-users/

 这里join（直接不保留空值）两次user表，计算订单取消数使用sum（if（，0，1））计算。

--方法1
SELECT temp1.request_at as Day,ROUND(SUM(IF(`status` = 'completed',0,1))/COUNT(id),2) as 'Cancellation Rate'
FROM (SELECT * FROM
(trips t  JOIN (SELECT * FROM users WHERE role = 'client' AND banned = 'NO') r on  t.client_id = r.users_id)) temp1 
  JOIN 
(SELECT * FROM users WHERE role='driver' AND banned = 'NO') temp2 on temp1.driver_id = temp2.users_id
GROUP BY temp1.request_at
HAVING temp1.request_at BETWEEN '2013-10-01' AND '2013-10-03'
--方法2
-- WHERE temp1.request_at
-- BETWEEN '2013-10-01' AND '2013-10-03'

-- select
--   t.request_at as 'Day',
--   # count(*) as 'Total',
--   # sum(if(t.status <> 'completed',1,0)) as 'Cancel',
--   round(sum(if(t.status <> 'completed', 1, 0)) / count(*), 2) as 'Cancellation Rate'
-- from
--   Trips as t
--   left join Users as c on t.client_id = c.users_id
--   left join Users as d on t.driver_id = d.users_id
-- where
--   t.request_at between '2013-10-01' and '2013-10-03'
--   and c.banned = 'No'
--   and d.banned = 'No'
-- group by
--   t.request_at

4.

602. 好友申请 II ：谁有最多的好友https://leetcode.cn/problems/friend-requests-ii-who-has-the-most-friends/

#Union all（合并不去重），Union合并去重
#找第几位（排序），使用order by + limit

SELECT id,num
from(
SELECT id,SUM(num) as num
FROM(
SELECT DISTINCT(requester_id) as id,COUNT(requester_id) as num
#, as num
FROM requestaccepted
GROUP BY requester_id
UNION ALL
SELECT DISTINCT(accepter_id)as id,COUNT(accepter_id) as num
FROM requestaccepted
GROUP BY accepter_id
)a
GROUP BY a.id)b
ORDER BY num DESC
LIMIT 1

5.

1158. 市场分析 Ihttps://leetcode.cn/problems/market-analysis-i/

 方法1:使用子查询，效率较低，方法2高。

join可以使用多个条件限定，在这里： on u.user_id = o.buyer_id and year(order_date)='2019'

#第一种
SELECT user_id as buyer_id,join_date,IFNULL(orders_in_2019,0)
FROM Users left JOIN 
(
SELECT DISTINCT buyer_id,COUNT(buyer_id) over(PARTITION BY buyer_id) AS orders_in_2019 
	FROM Orders 
	WHERE order_date>='2019-01-01' AND order_date<='2019-12-31'
)a on user_id=buyer_id 
#第二种
select user_id as buyer_id, join_date, count(order_id) as orders_in_2019
from Users as u left join Orders as o on u.user_id = o.buyer_id and year(order_date)='2019'
group by user_id

6.

1341. 电影评分https://leetcode.cn/problems/movie-rating/

 1.where 语句可以在group by 前使用先限定条件

  2.UNION ALL应该位于两个子查询之间，并且在每个子查询之后都需要添加括号。

  3.order by 可以使用两个字段，并且desc，asc灵活使用，先根据第一个字段排序后根据第二个字段排序

#初步的
( SELECT
	results 
	FROM
		(
		SELECT NAME AS
			results,
			dense_rank() over ( ORDER BY num DESC ) AS rank_m 
		FROM
			Users u
			JOIN ( SELECT user_id, count( user_id ) AS num FROM MovieRating GROUP BY user_id ) a ON u.user_id = a.user_id 
		) c 
	WHERE
		rank_m = 1 
	ORDER BY
		results 
		LIMIT 1 
	) UNION ALL
	(
	SELECT
		results 
	FROM
		(
		SELECT
			title AS results,
			dense_rank() over ( ORDER BY avg_rating DESC ) AS rank_r 
		FROM
			Movies m
			JOIN (
			SELECT
				movie_id,
				Avg( rating ) AS avg_rating 
			FROM
				( SELECT * FROM MovieRating WHERE YEAR ( created_at )= '2020' AND MONTH ( created_at )= '02' ) b 
			GROUP BY
				movie_id 
			) c ON m.movie_id = c.movie_id 
		) d 
	WHERE
		rank_r = 1 
	ORDER BY
	results 
	LIMIT 1)


#改进的
( SELECT NAME AS
	results 
	FROM
		Users u
		JOIN ( SELECT user_id, count( user_id ) AS num FROM MovieRating GROUP BY user_id ) a ON u.user_id = a.user_id 
	ORDER BY
		num DESC,
	NAME ASC 
		LIMIT 1 
	) UNION ALL
	(
	SELECT
		title AS results 
	FROM
		Movies m
		JOIN ( SELECT movie_id, Avg( rating ) AS avg_rating FROM MovieRating WHERE YEAR ( created_at )= '2020' AND MONTH ( created_at )= '02' GROUP BY movie_id ) c ON m.movie_id = c.movie_id 
	ORDER BY
		avg_rating DESC,
		title ASC 
	LIMIT 1 
	)

7.

1321. 餐馆营业额变化增长https://leetcode.cn/problems/restaurant-growth/

 datediff()函数限定两个时间间隔，sum（） over（order by ** rows 6 preceding）计算前7行求和

#未考虑不连续情况
SELECT
	* 
FROM
	(
	SELECT
		visited_on,
		sum( amount_day ) over ( ORDER BY visited_on rows 6 preceding ) AS amount,
		round( sum( amount_day ) over ( ORDER BY visited_on rows 6 preceding )/ 7, 2 ) AS average_amount 
		
	FROM
		( SELECT visited_on, sum( amount ) AS amount_day FROM Customer GROUP BY visited_on ) t1
	ORDER BY
		visited_on 
	) t2
WHERE
	datediff(
		visited_on,(SELECT min( visited_on ) FROM Customer ))>=6