leetcode SQL刷题

组合两个表

select FirstName, LastName, City, State from Person left join Address on Person.PersonId=Address.PersonId;

第二高的薪水

SELECT
	(CASE WHEN (SELECT COUNT(DISTINCT Salary) FROM (SELECT
			Salary
		FROM
			Employee
		ORDER BY
			Salary DESC
		LIMIT 2)a) <=1 THEN NULL ELSE Salary END) AS SecondHighestSalary
FROM
	(
		SELECT
			Salary
		FROM
			Employee
		ORDER BY
			Salary DESC
		LIMIT 2
	) a
ORDER BY
	Salary ASC
LIMIT 1

第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT
	distinct Salary AS getNthHighestSalary
FROM
	(
		SELECT
			@currt_Salary := Salary as Salary,
			@rank :=
		IF (
			@currt_Salary <>@prev_Salary ,@rank + 1 ,@rank
		) AS rank,
		@prev_Salary := @currt_Salary
	FROM
		(
			SELECT
				Salary
			FROM
				Employee
			ORDER BY
				Salary DESC
		) a,
		(
			SELECT
				@currt_Salary := 0 ,@prev_Salary := 0 ,@rank := 0
		) r
	) a
WHERE a.rank = N
      
  );
END

分数排名

SELECT
	Score,
CONVERT(Rank,SIGNED) AS Rank
FROM
	(
		SELECT
			@currt_score := Score AS Score,
			@Rank :=
		IF (
			@currt_score =@prev_score ,@Rank ,@Rank + 1
		) AS Rank,
		@prev_score := @currt_score
	FROM
		(
			SELECT
				Score
			FROM
				Scores
			ORDER BY
				Score DESC
		) a,
		(
			SELECT
				@currt_score := -1,
				@Rank := 0,
				@prev_score := -1
		) r
	) a

连续出现的数字

SELECT
	distinct Num AS ConsecutiveNums
FROM
	(
		SELECT
			@c_num := Num AS Num,
			@non := CONVERT (

				IF (@c_num =@p_num ,@non + 1, 1),
				SIGNED
			) AS non,
			@p_num := @c_num AS s
		FROM
			LOGS,
			(SELECT @c_num := 0) r,
			(SELECT @p_num := 0) s,
			(SELECT @non := 1) d
	) a
WHERE
	a.non >= 3;

超过经理收入的员工

SELECT a.`Name` AS Employee  FROM  Employee as a,Employee as b
WHERE a.ManagerId=b.Id AND a.Salary > b.Salary

查找重复的电子邮箱

SELECT Email FROM (
SELECT Email,COUNT(Email)as cnt FROM Person GROUP BY Email) a
WHERE a.cnt>1;

从不订购的客户

SELECT
	NAME as Customers
FROM
	Customers
WHERE
	Id NOT IN (
		SELECT DISTINCT
			CustomerId
		FROM
			Orders
	);

部门工资最高的员工

SELECT
	a.Department as Department,Employee.`Name` AS Employee,a.s as Salary
FROM
	Employee
INNER JOIN (
SELECT 
		Department.`Name` AS Department,
		a.DepartmentId,
		a.s
FROM Department INNER JOIN(
	SELECT
		DepartmentId,
		MAX(Salary) AS s
	FROM
		Employee AS e
	INNER JOIN Department AS d ON e.DepartmentId = d.Id
	GROUP BY
		DepartmentId) a ON Department.Id=a.DepartmentId
) a ON Employee.DepartmentId = a.DepartmentId
AND Employee.Salary = a.s

部门工资前三高的员工

SELECT b.`Name` AS Department,a.Name AS Employee,a.Salary AS Salary
FROM 
(SELECT Name,Salary,DepartmentId,
@currid := DepartmentId,
@curr_salary :=Salary,
@rank_id := IF(@currid=@prev_id,IF(@curr_salary=@prev_salary,@rank_id,@rank_id+1),1)as rid,
@prev_salary :=Salary,
@prev_id := @currid
FROM
(SELECT * FROM Employee
ORDER BY DepartmentId,Salary DESC) as a,
(SELECT @currid :=0,@rank_id :=0,@prev_id :=0)r)a
INNER JOIN Department as b
ON a.DepartmentId=b.Id
WHERE rid<=3

删除重复的电子邮箱

DELETE p1
FROM
	Person AS p1,
	Person AS p2
WHERE
	p1.Email = p2.Email
AND p1.Id > p2.Id

上升的温度

SELECT w2.Id FROM Weather as w1,Weather as w2
WHERE
DATEDIFF(w2.RecordDate,w1.RecordDate) = 1 AND w2.Temperature>w1.Temperature

行程和用户

# Write your MySQL query statement below
SELECT
	b.Request_at AS 'Day',
	IFNULL(ROUND(cancel / totally, 2),0.00) AS 'Cancellation Rate'
FROM
(
		SELECT
			Request_at,
			IFNULL(COUNT(*),0) AS totally
		FROM
			(
				SELECT
					*
				FROM
					Trips
				WHERE
					Client_Id NOT IN (
						SELECT
							Users_Id
						FROM
							Users
						WHERE
							Banned = 'Yes'
					)
				AND Driver_Id NOT IN (
					SELECT
						Users_Id
					FROM
						Users
					WHERE
						Banned = 'Yes'
				)
			) a
		WHERE a.Request_at IN ('2013-10-01','2013-10-02','2013-10-03')
		GROUP BY
			Request_at
	) b
LEFT JOIN
(
	SELECT
		Request_at,
	  IFNULL(COUNT(*),0) AS cancel
	FROM
		(
			SELECT
				*
			FROM
				Trips
			WHERE
				Client_Id NOT IN (
					SELECT
						Users_Id
					FROM
						Users
					WHERE
						Banned = 'Yes'
				)
			AND Driver_Id NOT IN (
				SELECT
					Users_Id
				FROM
					Users
				WHERE
					Banned = 'Yes'
			)
		) a
	WHERE
		a. STATUS <> 'completed' AND a.Request_at IN ('2013-10-01','2013-10-02','2013-10-03')
	GROUP BY
		Request_at
)a 
ON b.Request_at = a.Request_at

大的国家

SELECT `name`,population,area FROM World WHERE population>25000000 OR area>3000000;

超过5名学生的课

SELECT class FROM (
SELECT class,COUNT(DISTINCT class,student) AS num FROM
courses
GROUP BY class
)a
WHERE a.num>=5

体育馆的人流量

# Write your MySQL query statement below
SELECT
	id,
	date,
	people FROM (
SELECT
	id,
	date,
	people
FROM
	(
		SELECT
			a.*, @rank :=IF (@prev_id = id - 1, @rank + 1, 1) AS rank,
		@prev_id := id AS prev_num
	FROM
		(
			SELECT
				*
			FROM
				stadium
			WHERE
				people >= 100
		) AS a,
 (select @rank:=0)r,
 (select @prev_id:=0)n
	) a
WHERE
	a.rank > 3
UNION
	SELECT
		id,
		date,
		people
	FROM
		(
			SELECT
				*, @rank :=
			IF (@prev_id = id - 1 ,@rank + 1, 1) AS rank,
			@prev_id := id
		FROM
			(
				SELECT
					*
				FROM
					stadium
				WHERE
					people >= 100
			) a,
			(select @rank:=0)r,
			(select @prev_id:=0)n
		) a
	INNER JOIN (
		SELECT
			id - 2 AS start_id,
			id AS end_id
		FROM
			(
				SELECT
					*, @rank :=
				IF (@prev_id = id - 1 ,@rank + 1, 1) AS rank,
				@prev_id := id
			FROM
				(
					SELECT
						*
					FROM
						stadium
					WHERE
						people >= 100
				) a,
				 (select @rank:=0)r,
				 (select @prev_id:=0)n
			) a
		WHERE
			a.rank = 3
	) b ON a.id BETWEEN b.start_id
	AND b.end_id
) a
ORDER BY a.date ASC;

有趣的电影

SELECT * FROM cinema WHERE description<>'boring' AND id%2=1
ORDER BY rating DESC

换座位

SELECT agj_id as id,student
FROM
(
SELECT *,
@curr:=id,
@max_id:=(SELECT max(id) from seat),
@adjust_id:=IF(@curr<@max_id,IF((@curr)%2=1,id+1,id-1),IF((@curr)%2=1,id,id-1)) as agj_id       
FROM
seat,
(SELECT @curr:=0,@adjust_id:=0)r)b
ORDER BY id ASC

交换工资

UPDATE salary SET sex=CASE WHEN sex='m' THEN 'f' ELSE 'm' END;

最后再放一张通过情况截图，上述题目有任何不清楚的地方欢迎留言，有空会一一回复。