MySQL leetCode 练习题-CSDN博客

本文链接：https://blog.csdn.net/m0_57440463/article/details/134050628
自己的笔记过程
-- 查询数据 p1数据 > p2的数据 可以进行排名 过滤 之类的数据
SELECT
* 
FROM
	person AS p1,
	( SELECT person.email email, COUNT( person.email ) count FROM person GROUP BY person.email ) AS p2 
WHERE
	p1.email = p2.email 
-- 上升的温度 # Write your MySQL query statement below
SELECT
	w2.id 
FROM
	Weather AS w1,
	Weather AS w2 
WHERE
	w2.Temperature > w1.Temperature 
	AND datediff( w2.recordDate, w1.recordDate ) = 1;
SELECT
	w1.id 
FROM
	weather AS w1,
	weather AS w2 
WHERE
	w1.Temperature > w2.Temperature 
	AND DATEDIFF( w1.recordDate, w2.recordDate ) = 1;
-- 行程与用户 //写 SQL 先说明思路 然后在进行狡辩 先排除掉 禁用客户数据 /全部数据
SELECT
t.request_at 'Day',
round( sum( IF ( t.STATUS = 'completed', 0, 1 ) ) / count( t.STATUS ), 2 ) AS 'Cancellation Rate' 
FROM
	trips AS T
	JOIN users AS u1 ON ( t.client_id = u1.users_id AND u1.banned = 'No' )
	JOIN users AS u2 ON ( t.driver_id = u2.users_id AND u2.banned = 'No' ) 
WHERE
	t.request_at BETWEEN '2013-10-01' 
	AND '2013-10-03' 
GROUP BY
	t.request_at 
ORDER BY
	t.request_at ASC;
-- 游戏玩法分析 I
SELECT
* 
FROM
	activity AS a1
	LEFT JOIN activity AS a2 ON SELECT
	a1.player_id,
	MIN( a1.event_date ) AS fiest_login 
FROM
	activity AS a1 
GROUP BY
	a1.player_id;
-- 游戏玩法 1.计算出第二天登录的人 2.在计算出总人数 3.round方法 
SELECT
count( a1.player_id ) 
FROM
	activity AS a1,
	activity AS a2 
WHERE
	DATEDIFF( a1.event_date, a2.event_date ) = 1 
	AND a1.player_id = a2.player_id;
-- 思路错误 我这个是临近俩天登录 
SELECT
ROUND( COUNT( DISTINCT a1.player_id ) / COUNT( DISTINCT a.player_id ), 2 ) AS 'fraction' 
FROM
	activity AS a,
	activity AS a1
	JOIN activity AS a2 ON a1.player_id = a2.player_id 
WHERE
	DATEDIFF( a1.event_date, a2.event_date ) = 1;
-- 先找出首次登录的日期 然后找出与首次登录日期相近的相近的登录 
-- sql 执行顺序 
-- from
-- JOIN 
-- WHERE
-- SELECT 
-- GROUP BY
-- HAVING
-- ORDER BY
SELECT
	ROUND( count( a.player_id ) / count( b.player_id ), 2 ) AS 'fraction' 
FROM
	( SELECT player_id, min( event_date ) event_date FROM activity GROUP BY player_id ) AS b
	LEFT JOIN activity AS a ON a.player_id = b.player_id 
	AND DATEDIFF( a.event_date, b.event_date ) = 1;
-- 262.行程与用户
EXPLAIN SELECT
t.request_at 'Day',
round( sum( IF ( t.STATUS = 'completed', 0, 1 ) ) / count( t.STATUS ), 2 ) AS 'Cancellation Rate' 
FROM
	trips AS T
	JOIN users AS u1 ON ( t.client_id = u1.users_id AND u1.banned = 'No' )
	JOIN users AS u2 ON ( t.driver_id = u2.users_id AND u2.banned = 'No' ) 
WHERE
	t.request_at BETWEEN '2013-10-01' 
	AND '2013-10-03' 
GROUP BY
	t.request_at 
ORDER BY
	t.request_at ASC;
-- 570.至少有5名直属经理 //首先记录 manager 次数超过 5次 通过记录 去确定 NAME // jion 只包含俩个数据 才显示表 LEFT RIGHT 以左右俩次表为主 可以为空 //因为关联的关系 显示的表导致数据不一样 主要还是关联的条件

SELECT
	groupby orderby
CASE
		
		WHEN em.NAME IS NULL THEN
		'----' ELSE em.NAME 
	END AS `name` SELECT
	* 
FROM
	employee AS em
	RIGHT JOIN ( SELECT e.managerId, count( e.managerId ) count FROM employee AS e GROUP BY e.managerId HAVING count >= 5 ) AS emb ON em.id = emb.managerId;
-- 577.员工奖金 
SELECT
e.NAME,
b.bonus 
FROM
	employee AS e
	LEFT JOIN bonus AS b ON e.empId = b.empId 
WHERE
	b.bonus < 1000 
	OR b.bonus IS NULL;
--  584.寻找用户 
SELECT NAME 
FROM
	customer 
WHERE
	referee_id != '2' 
	OR referee_id IS NULL;
-- 585.2016年工资 
SELECT
* 
FROM
	Insurance AS a
	LEFT JOIN Insurance AS b ON a.tiv_2015 = b.tiv_2015 
	AND a.tiv_2016 != b.tiv_2016 
	AND a.pid != b.pid;
-- 586.订单最多的客户 
SELECT
customer_number 
FROM
	orders 
GROUP BY
	customer_number 
ORDER BY
	count( customer_number ) DESC 
	LIMIT 1;
-- 大的国家 
SELECT NAME
,
population,
area 
FROM
	world 
WHERE
	area >= 3000000 
	OR population >= 25000000;
-- 506.超过5名学生的课 
SELECT
class 
FROM
	courses 
GROUP BY
	class 
HAVING
	count( class ) >= 5;
-- 查找出 人数最高的次数 重点痛点 : ROW_NUMBER ( ) OVER ( ORDER BY id ) 是一个窗口函数，它根据 "id" 列的值对结果集中的行进行排序，并为每一行分配一个行号（行号从 1开始递增，按照 "id" 的顺序分配）。这个窗口函数不会改变结果集中的行的顺序，但它为每一行分配一个行号 
WITH t1 AS ( SELECT *, id - row_number ( ) over ( ORDER BY id ) AS rk FROM stadium WHERE people >= 100 ) SELECT
id,
visit_date,
people 
FROM
	t1 
WHERE
	rk IN ( SELECT rk FROM t1 GROUP BY rk HAVING count( rk ) >= 3 );
-- 好有申请,谁有最多的好友 
-- ORDER BY LIMIT 分组根据排序结果集  进行分页 
SELECT
	ids AS id,
	count( ids ) AS num 
FROM
	( SELECT requester_id AS ids FROM requestaccepted UNION ALL SELECT accepter_id FROM requestaccepted ) AS t1 
GROUP BY
	t1.ids 
ORDER BY
	num DESC 
	LIMIT 1;
// 树节点
-- CASE WHEN 多条件过滤 
SELECT
		t.id,
	CASE
			WHEN t.id = ( SELECT id FROM tree WHERE p_id IS NULL ) THEN
			'Root' 
			WHEN t.id IN ( SELECT p_id FROM tree ) THEN
			'Inner' ELSE 'left' 
		END AS Type 
	FROM
		tree AS t 
	ORDER BY
		t.id;
	//判断三角形 
	SELECT
	*,
	CASE
			WHEN x + y > z THEN
			'true' ELSE 'false' 
		END AS Etriangle 
	FROM
		triangle;
	//修改性别 及其状态 
	UPDATE salary 
	SET sex =
	IF
		( sex = 'f', 'm', 'f' );
	UPDATE salary 
	SET sex =
	CASE
			sex 
			WHEN 'f' THEN
			'm' ELSE 'f' 
		END;
-- 1045.购买所有产品 1.错误点 比较每个用户的总的商品量 ( 去重 ) 不是用户买的总的商品量 ( 可能每个用户对于一个商品进行多买 ) count记录值那个字段都可以 
	SELECT
	customer_id 
	FROM
		customer 
	GROUP BY
		customer_id 
	HAVING
		count( DISTINCT product_key ) = ( SELECT count( product_key ) FROM product );
	SELECT
		customer_id,
		count( customer_id ) 
	FROM
		customer 
	GROUP BY
		customer_id;
	SELECT
		customer_id,
		count( DISTINCT product_key ) 
	FROM
		customer 
	GROUP BY
		customer_id;
-- 	1050.至少合作过3次的选手 思路 :查询全部的高于 3次的演员 
-- 	盲点 :如果是多表使用 
-- 	GROUP BY	要指定表的名称 单表则不使用
		SELECT
		a1.actor_id AS actor_id,
		a1.director_id AS director_id,
		a1.TIMESTAMP AS TIMESTAMP 
	FROM
		actordirector AS a1
		LEFT JOIN actordirector AS a2 ON a1.actor_id = a2.actor_id 
		AND a1.director_id = a2.director_id 
	GROUP BY
		a1.TIMESTAMP,
		a1.actor_id,
		a1.director_id 
	HAVING
		count( a1.TIMESTAMP ) >= 3;
-- 	同时对每个字段进行分组 就能过滤相同 字段的次数 
-- 	例如 :只过滤一个字段 出现的次数为 5次 那么再过滤一个字段出现是三次 那么最终出现的是三次
	SELECT
	actor_id,
	director_id,
	count( TIMESTAMP ) 
	FROM
		ActorDirector 
	GROUP BY
		actor_id,
		director_id 
	HAVING
		COUNT( TIMESTAMP ) >= 3;
	SELECT
		actor_id,
		director_id,
		TIMESTAMP,
		COUNT( TIMESTAMP ) AS timestamp_count 
	FROM
		ActorDirector 
	GROUP BY
		actor_id,
		director_id,
		TIMESTAMP;
--  1070.销售分析 III todo :窗口函数回头复习 
-- 	GROUP BY
-- 		分组之后 不能对其他 clounms 进行展示 
-- 	GROUP BY
-- 		作用的只有分组列和聚合函数列，其他列不管用，返回的其他列只有对应的第一行
		SELECT
		p.product_id,
		s.YEAR AS first_year,
		quantity,
		price 
	FROM
		sales AS s
		LEFT JOIN product AS p ON s.product_id = p.product_id 
	WHERE
		( s.product_id, YEAR ) IN ( SELECT product_id, min( YEAR ) FROM sales GROUP BY product_id );
	SELECT
		product_id,
		min( YEAR ) AS first_year,
		quantity,
		price 
	FROM
		sales 
	GROUP BY
		product_id 
-- // 1075.项目员工 SELECT
		project_id,
		ROUND( AVG( experience_years ), 2 ) average_years 
	FROM
		project AS p
		JOIN employee AS e ON p.employee_id = e.employee_id 
	GROUP BY
		project_id;
-- 	// 1084.销售分析 思路 : 要想查询单位时间内的出现的数据 其他时间出现的不算 就是单位时间内出现的次数 =出现的总数 我之前错误的思路是因为只有单位时间内出现一次 才能算这个商品
	SELECT
	product_id,
	product_name 
	FROM
		product 
	WHERE
		product_id IN (
		SELECT
			product_id 
		FROM
			sales 
		WHERE
		product_id IN ( SELECT product_id FROM sales GROUP BY product_id HAVING count( sale_date BETWEEN '2019-01-01' AND '2019-03-31' OR NULL ) = count( product_id ) ) 
	);