LeetCode_MySQL
以下内容多数参照 https://blog.csdn.net/WhereIsHeroFrom/article/details/85088151
select 函数的完整语法：
1 select [ALL|DISTINCT|DISTINCTROW|TOP] 2 {*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]} 3 FROM tableexpression[,…][IN externaldatabase] 4 [WHERE…] 5 [GROUP BY…] 6 [HAVING…] 7 [ORDER BY…]
175. 组合两个表
1 select FirstName, LastName, City, State 2 from Person left join Address 3 on Person.PersonId = Address.PersonId 4 ;
176. 第二高的薪水
1 SELECT 2 CASE c.cnt 3 WHEN 0 THEN 4 NULL 5 ELSE 6 c.Salary 7 END AS SecondHighestSalary 8 FROM 9 (SELECT COUNT(Salary) AS cnt, Salary 10 FROM 11 (SELECT DISTINCT Salary 12 FROM Employee 13 ORDER BY Salary DESC LIMIT 1, 1 14 ) AS b 15 ) AS c
1 SELECT 2 (SELECT DISTINCT 3 Salary 4 FROM 5 Employee 6 ORDER BY Salary DESC 7 LIMIT 1 OFFSET 1) AS SecondHighestSalary 8 ;
1 SELECT 2 IFNULL( 3 (SELECT DISTINCT Salary 4 FROM Employee 5 ORDER BY Salary DESC LIMIT 1, 1 6 ), 7 NULL) SecondHighestSalary
1 SELECT 2 IFNULL( 3 (SELECT DISTINCT Salary 4 FROM Employee 5 ORDER BY Salary DESC 6 LIMIT 1 OFFSET 1), 7 NULL) AS SecondHighestSalary
| IFNULL(v1,v2) | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 |
SELECT IFNULL(null,'Hello Word')->Hello Word
|
LIMIT语法：SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
1 mysql> SELECT * FROM table LIMIT 2,4; // 检索 3-6 行 2 mysql> SELECT * FROM table LIMIT 6,-1; // 检索 7 之后所有行 3 mysql> SELECT * FROM table LIMIT 5; //检索前 5 行，相当于 SELECT * FROM table LIMIT 0,5; 4 mysql> SELECT * FROM table LIMIT 2 OFFSET 3;//查询4-5两条记录，想当于 SELECT * FROM orange LIMIT 3,2;
1 CASE expression 2 WHEN condition1 THEN result1 3 WHEN condition2 THEN result2 4 ... 5 WHEN conditionN THEN resultN 6 ELSE result 7 END
CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。
177. 第N高的薪水
1 CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT 2 BEGIN 3 RETURN ( 4 select (IF( 5 (select count(*) from (select distinct e.Salary from Employee e) e)>=N, 6 (select min(e.Salary) from (select distinct e.Salary from Employee e order by e.Salary desc limit N) e) 7 , NULL 8 )) 9 10 ); 11 END
1 CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT 2 BEGIN 3 set N = N - 1; 4 RETURN ( 5 select 6 case c.cnt 7 when 0 then 8 null 9 else 10 c.Salary 11 end as xx 12 from 13 (select count(Salary) as cnt, Salary 14 from 15 (select distinct Salary 16 from Employee 17 order by Salary desc limit N, 1 18 ) as b 19 ) as c 20 ); 21 END
1 CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT 2 BEGIN 3 set N = N-1; 4 RETURN ( 5 select distinct ifnull(salary,null) from employee order by salary desc limit N,1 6 ); 7 END
CREATE FUNCTION function_name ([func_parameter[,…]])
RETURNS {STRING|INTEGER|REAL|DECIMAL|…}
routine_body - 函数体
默认情况下自定义函数与当前数据库关联。如果想要把该函数与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.function_name。
自定义用户变量:
可以先在用户变量中保存值然后在以后引用它；这样可以将值从一个语句传递到另一个语句。用户变量与连接有关。也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。
用户变量的形式为@var_name，其中变量名var_name可以由当前字符集的文字数字字符、‘.’、‘_’和‘$’组成。 默认字符集是cp1252 (Latin1)。可以用mysqld的–default-character-set选项更改字符集。用户变量名对大小写不敏感。
设置用户变量的一个途径是执行SET语句：
1 SET @var_name = expr [, @var_name = expr] ...
对于SET，可以使用=或:=作为分配符。分配给每个变量的expr可以为整数、实数、字符串或者NULL值。
也可以用语句select代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较 操作符：
1 mysql> SET @t1=0, @t2=0, @t3=0; 2 mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
函数体
- 函数体由合法的SQL语法构成;
- 函数体可以是简单的SELECT或INSERT语句;
- 函数体如果为复合结构则使用BEGIN…END语句；
- 复合结构可以包括声明，循环，控制结构。
178. 分数排名
1 select Score, 2 (select count(distinct Score) 3 from Scores as b 4 where a.score < b.score 5 ) + 1 as Rank 6 from Scores as a order by Rank;
1 select distinct a.num ConsecutiveNums from Logs a,(select @val:=0,@count:=0) x 3 where if( (@val=a.num), 4 if( (@count:=@count+1) and (@count>=2) ,1, 0 ), 5 (( @count:=@count-@count) or (@val:=a.num)) and 0 6 )
1 select e1.Name as Employee 2 from Employee e1, 3 Employee e2 4 where e1.ManagerId = e2.Id 5 AND e1.Salary > e2.Salary;
182. 查找重复的电子邮箱
1 select Email 2 from 3 (select Id, Email, count(Email) as e 4 from Person group by Email 5 ) as x 6 where x.e > 1;
1 select Email 2 from Person 3 group by Email 4 having count(Email) > 1
183. 从不订购的客户
1 select Name Customers 2 from Customers 3 where Id in( 4 select Id 5 from Customers 6 where Id not in( 7 select CustomerId 8 from Orders) 9 )
1 select c.Name as Customers 2 from Customers as c 3 where c.Id not in 4 (select n.CustomerId 5 from 6 (select CustomerId, c.Id, Name from 7 Orders o, 8 Customers c 9 where c.Id = CustomerId 10 ) as n 11 )
1 select Name as Customers 2 from Customers 3 where Id not in 4 ( 5 select CustomerId 6 from Orders 7 )
184. 部门工资最高的员工
select d.Name as Department, c.Employee, c.Salary from (select a.DepartmentId, a.Name as Employee, a.Salary as Salary from Employee as a, (select DepartmentId, max(Salary) as Salary from Employee group by DepartmentId ) as b where a.Salary = b.Salary and a.DepartmentId = b.DepartmentId ) as c, Department as d where d.Id = c.DepartmentId
1 select 2 d.Name as Department, 3 e.Name as Employee, 4 e.Salary 5 from 6 Employee e,Department d 7 where 8 e.DepartmentId=d.id 9 and 10 (e.Salary,e.DepartmentId) 11 in ( 12 select 13 max(Salary),DepartmentId 14 from 15 Employee 16 group by 17 DepartmentId 18 )
1 select 2 d.Name as Department, 3 e.Name as Employee, 4 Salary AS Salary 5 from 6 Employee e inner join Department d 7 on e.DepartmentId=d.Id 8 where 9 Salary in ( 10 select 11 max(Salary) as Salary 12 from 13 Employee e1 14 where 15 e1.DepartmentId=e.DepartmentId 16 )
185. 部门工资前三高的员工
2 select 3 d.Name as Department, h.Employee, h.Salary 4 from 5 (select 6 g.DepartmentId, g.Name as Employee, g.Salary 7 from 8 ( 9 select a.Id 10 from 11 (select e1.Id, count(distinct e2.Salary) as cnt 12 from 13 Employee e1, 14 Employee e2 15 where e1.DepartmentId = e2.DepartmentId 16 and ( 17 e1.Salary <= e2.Salary 18 ) 19 group by e1.Id 20 ) as a 21 where cnt <= 3 22 ) as b 23 left join Employee g 24 on b.Id = g.Id 25 ) as h 26 left join Department d 27 on h.DepartmentId = d.Id 28 where not IsNull(d.Name) 29 order by h.DepartmentId, h.Salary desc 30 ;
1 SELECT 2 P2.Name AS Department,P3.Name AS Employee,P3.Salary AS Salary 3 FROM 4 Employee AS P3 5 INNER JOIN 6 Department AS P2 7 ON 8 P2.Id = P3.DepartmentId 9 WHERE ( 10 SELECT 11 COUNT(DISTINCT Salary) 12 FROM 13 Employee AS P4 14 WHERE 15 P3.DepartmentId = P4.DepartmentId 16 AND 17 P4.Salary >= P3.Salary 18 ) <= 3 19 ORDER BY 20 DepartmentId,Salary DESC
1 select 2 B.name as Department , A.Name as Employee, A.Salary 3 from ( 4 select 5 Name, DepartmentId, Salary, DENSE_RANK() over(partition by DepartmentId order by Salary desc) as pm 6 from 7 Employee 8 ) A, Department B 9 where 10 A.pm<=3 and A.DepartmentId = B.Id 11 order by 12 B.id asc, A.Salary desc
SQL中的窗口函数 OVER窗口函数
Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）简介
187. 重复的DNA序列
1 DELETE p1 FROM Person p1, 2 Person p2 3 WHERE 4 p1.Email = p2.Email AND p1.Id > p2.Id
1 delete 2 from 3 Person 4 where Id not in 5 ( 6 select 7 * 8 from 9 (select min(Id) as Id 10 from Person 11 group by Email 12 ) as x 13 );
197. 上升的温度
2 select 3 a.Id 4 from 5 Weather a, 6 Weather b 7 where 8 a.RecordDate in (select interval 1 day + b.RecordDate) 9 and 10 a.Temperature > b.Temperature;
1 SELECT 2 weather.Id AS Id 3 FROM 4 weather 5 JOIN 6 weather w ON DATEDIFF(weather.RecordDate, w.RecordDate) = 1 7 AND weather.Temperature > w.Temperature
DATEDIFF(date1,date2) 函数返回两个日期之间的天数(date1-date2)。
262. 行程和用户
1 select 2 Day, round( if(IsNull(up),0,up) / if(IsNull(dwn),1,dwn), 2 ) as 'Cancellation Rate' 3 from 4 ( 5 select 6 allTrips.Request_at as Day, allTrips.cnt as dwn, allCancellTrips.cnt as up 7 from 8 ( 9 select 10 Request_at, count(*) as cnt 11 from 12 ( 13 select 14 ta.Id, ta.Client_Id, ta.Request_at 15 from 16 Trips ta 17 left join 18 Users ua 19 on 20 ta.Client_Id = ua.Users_Id 21 where 22 ua.Banned = "No" 23 ) as noBannedTrips 24 group by 25 Request_at 26 ) as allTrips 27 left join 28 ( 29 select 30 Request_at, count(*) as cnt 31 from 32 ( 33 select 34 ta.Id, ta.Client_Id, ta.Request_at 35 from 36 Trips ta 37 left join 38 Users ua 39 on 40 ta.Client_Id = ua.Users_Id 41 where 42 ua.Banned = "No" 43 and 44 ta.Status != "completed" 45 ) as noBannedAndNotComletedTrips 46 group by 47 Request_at 48 ) as allCancellTrips 49 on 50 allTrips.Request_at = allCancellTrips.Request_at 51 ) as result 52 where 53 result.Day >= '2013-10-01' 54 and 55 result.Day <= '2013-10-03';
1 select 2 t.Request_at as Day, 3 round( 4 count( if(t.Status != 'completed',1,null)) / count(t.Status),2 ) 5 as 'Cancellation Rate' 6 from 7 Trips as t 8 left join 9 Users as u1 10 On 11 t.Client_Id = u1.Users_Id 12 left join 13 Users as u2 14 on 15 t.Driver_Id = u2.Users_Id 16 where 17 u1.Banned = 'No' 18 and 19 u2.Banned = 'No' 20 and 21 t.Request_at between '2013-10-01' and '2013-10-03' 22 23 group by Day 24 order by Day
1 SELECT 2 t1.Request_at AS DAY, 3 ROUND(((count( CASE WHEN STATUS = 'cancelled_by_driver' THEN 1 ELSE NULL END ) + count( CASE WHEN STATUS = 'cancelled_by_client' THEN 1 ELSE NULL END )) 4 / count( STATUS )),2) AS 'Cancellation Rate' 5 FROM 6 Trips t1 7 INNER JOIN Users t2 ON t1.Client_Id = t2.Users_Id 8 INNER JOIN Users t3 ON t1.Driver_Id = t3.Users_Id 9 WHERE 10 t2.Banned = 'No' 11 AND t3.Banned = 'No' 12 AND t1.Request_at >= '2013-10-01' 13 AND t1.Request_at <= '2013-10-03' 14 GROUP BY 15 t1.Request_at 16 ORDER BY DAY
1 SELECT 2 request_at as Day, 3 round( 4 count(if(t.status<>'completed',t.status,NULL)) -- 当天非禁止用户的取消数量 5 /sum(if(t.status,1,0)) -- 当天订单总数 6 , 7 2)as 'Cancellation Rate' 8 FROM 9 users u 10 INNER JOIN trips t 11 on 12 u.users_id = t.client_id 13 and t.request_at BETWEEN '2013-10-01' and '2013-10-03' 14 and u.banned = 'NO' 15 group by t.request_at
595. 大的国家
1 select 2 name,population,area 3 from 4 World 5 where 6 area>3000000 or population >25000000
596. 超过5名学生的课
1 select 2 class 3 from 4 courses 5 group by 6 class 7 having 8 count(distinct student) >= 5
sql中where和having的区别
Where中不能使用聚合函数，Having中可以使用聚合函数。
601. 体育馆的人流量
1 MySQL自定义排序+本题部分思路 2 select 3 id,visit_date+@curRank as date,people,@curRank := @curRank + 1 AS rank 4 from stadium,( 5 SELECT @curRank := 0 6 ) q 7 where 8 people >100 9 ORDER BY visit_date desc
1 select t.id, t.date, t.people 2 from 3 ( 4 select distinct 5 s1.id 6 from 7 stadium s1, 8 stadium s2, 9 stadium s3 10 where (s1.id + 1 = s2.id 11 and s2.id + 1 = s3.id 12 and s1.people >= 100 13 and s2.people >= 100 14 and s3.people >= 100) 15 or (s1.id - 1 = s2.id 16 and s2.id - 1 = s3.id 17 and s1.people >= 100 18 and s2.people >= 100 19 and s3.people >= 100) 20 or (s1.id + 1 = s3.id 21 and s1.id - 1 = s2.id 22 and s1.people >= 100 23 and s2.people >= 100 24 and s3.people >= 100) 25 ) as a 26 left join stadium t 27 on a.id = t.id;
620. 有趣的电影
1 select 2 id,movie,description,rating 3 from 4 cinema 5 where 6 id % 2 = 1 7 and 8 description <> 'boring' 9 order by 10 rating desc
626. 换座位
1 select 2 a.id, if( a.id in (select count(*) from seat) and a.id%2=1, a.student, b.student ) as student 3 from 4 ( 5 select id, student, (FLOOR((id+1)/2)*2-(1-id%2)) as next 6 from seat 7 ) as a 8 left join seat b 9 on a.next = b.id 10 order by a.id;
1 SELECT * FROM( 2 SELECT id-1 AS id,student FROM seat WHERE id%2=0 3 UNION 4 SELECT id+1 AS id,student FROM seat WHERE id%2=1 AND (id+1) <= (SELECT COUNT(*) FROM seat) 5 UNION 6 SELECT id AS id,student FROM seat WHERE id%2=1 AND (id+1) > (SELECT COUNT(*) FROM seat) 7 ) AS T1 8 ORDER BY id ASC
FLOOR(x):返回小于或等于 x 的最大整数(SELECT FLOOR(1.5) -- 小于或等于 1.5 的整数：返回1)
627. 交换工资
2 salary 3 set 4 sex=if(sex='m','f','m')
