没有找到怎么按照mysql高亮显示...
- left join的使用
select
p.FirstName,
p.LastName,
A.City,
A.State
from
Person p
left join
Address A
on p.PersonId = A.PersonId
- Second Highest Salary:
- IFNULL(A,B)
- Order by Salary Desc
- Limit 1,1
Select
IFNULL((select
distinct Salary
from
Employee
order by Salary Desc
Limit 1,1), NULL) AS SecondHighestSalary
- Nth Highest Salary
- 自定义sql函数
Create Function getNthHighestSalary(N INT) Returns INT
BEGIN
Declare M INT;
SET M = N-1;
RETURN(
Select IFNULL((Select Distinct Salary from Employee
order by Salary DESC
Limit M,1), NULL)
);
END
- Rank Scores:
Rank函数：Row_number(), Dense_rank()
如果不用dense_rank()， 建立一张disinct 某数值的表，计算比当前值大于等于的记录条数
select
Score, (select
count(*)
from
(select
distinct Score S
from
Scores) AS Tmp
where S >= Score) AS Rank
from Scores
order by Rank
- Consecutive Numbers
多几张同样的表格
select
distinct l1.Num AS ConsecutiveNums
from
logs l1,
logs l2,
logs l3
where
l1.Id = l2.Id-1 and
l2.Id = l3.Id-1 and
l1.Num = l2.Num and
l2.Num = l3.Num
- Employees Earning More Than Their Managers
select
e1.Name As Employee
from
Employee e1 inner join Employee e2
on e1.ManagerId = e2.Id
where e1.Salary > e2.Salary
- Duplicate Emails
select
Email
from
Person
group by Email
having count(*) >= 2
- Customers Who Never Order
使用NOT IN 即可
select
c.Name as Customers
from
Customers c
where
c.Id not in (select CustomerId from Orders)
- Department Highest Salary
先建立每个部分薪水最高的人员名单；再用大表匹配该名单即可( ) IN ()
select
de.Name AS Department , e.Name AS Employee, e.Salary
from
Employee e inner join Department de
on e.DepartmentId = de.Id
where
(e.salary, e.DepartmentId) IN
(select max(salary), DepartmentId from Employee group by DepartmentId)
- Department Top Three Salaries ***
两次使用表employee，筛选符合前三名的人选
select
de.Name AS 'Department',
e.Name AS 'Employee',
e.Salary
From
Employee e
inner join
Department de
on e.DepartmentId = de.Id
where
(select
count(distinct e2.Salary) # 计算e2表的不同salary条数
from
Employee e2 # 两次使用表employee
where
e.Salary < e2.Salary
and e.DepartmentId = e2.DepartmentId # 两个表中部门相同的部分，e2比e薪水高的记录
) < 3 # 前三名
;
- Delete Duplicate Emails
仅保留相同 id 中最小的一个
mysql出现You can’t specify target table for update in FROM clause 这个错误的意思是不能在同一个sql语句中，先select同一个表的某些值，然后再update这个表。
Delete from Person
where
Id not in
(select
min(p1.Id)
from
(select * from Person) p1 ## 这里不能直接写为 Person p1, 会出现报错You can’t specify target table for update in FROM clause
group by p1.Email
)
- Rising Temperature
注意日期差要用函数Datediff()： Datediff(date1, date2) = 1表示 date1 比 date2 晚一天
Select
w2.Id
from
Weather w1,
Weather w2
Where
DATEDIFF(w1.RecordDate, w2.RecordDate) = -1
And
w1.Temperature < w2.Temperature
- Trips and Users ***
我自己的解法：
select
b.Request_at AS Day,
ROUND(count(distinct a.Id)/count(distinct b.Id) , 2) AS 'Cancellation Rate'
From # 结合两张表查处Banned = 'NO' 的用户，包括Client和driver
(
select
t.*,
u1.*,
u2.Users_Id AS Users_Id2, u2.Banned AS Banned2, u2.Role AS Role2
from
Trips t inner join Users u1
ON t.Client_Id = u1.Users_Id
inner join Users u2
On t.Driver_Id = u2.Users_Id
where
u1.Role = 'client'
and u2.Role = 'driver'
and u1.Banned = 'No'
and u2.Banned = 'No'
# and t.Status != 'completed'
) AS b
Left Join
# 同上，加一个条件 status ！= 'completed'
(
select
t.*, u1.*, u2.Users_Id AS Users_Id2, u2.Banned AS Banned2, u2.Role AS Role2
from
Trips t inner join Users u1
ON t.Client_Id = u1.Users_Id
inner join Users u2
On t.Driver_Id = u2.Users_Id
where
u1.Role = 'client'
and u2.Role = 'driver'
and u1.Banned = 'No'
and u2.Banned = 'No'
and t.Status != 'completed'
) AS a
on b.Request_at = a.Request_at
where
b.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
group by 1；
看到的其他解法一：
这里使用了
Case When
匹配 LIKE 'cancelled%'
简化了上面的重复写了两张表
但是这里只匹配了Client，没有匹配driver，个人认为应该再Join一次driver
SELECT
t.Request_at Day,
ROUND(SUM(CASE WHEN t.Status LIKE 'cancelled%' THEN 1 ELSE 0 END)/COUNT(*), 2) 'Cancellation Rate'
FROM
Trips t JOIN Users u
ON t.Client_Id = u.Users_Id
AND u.Banned = 'No'
WHERE
t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.Request_at;
其他解法二：
这里使用了
IF(expr, res1(if, ture), res2 (if False))
同上，只匹配了Client
SELECT
Request_at Day,
ROUND(COUNT(IF(Status != 'completed', TRUE, NULL)) / COUNT(*), 2) 'Cancellation Rate'
FROM
Trips
WHERE
(Request_at BETWEEN '2013-10-01' AND '2013-10-03')
AND Client_Id IN
(SELECT Users_Id FROM Users WHERE Banned = 'No') GROUP BY Request_at;
- Big Countries
select
name,
population,
area
from
World
where
area > 3000000
or population > 25000000
- Classes More Than 5 Students
select
class
from
courses
group by class
having count(distinct student) >= 5
- Human Traffic of Stadium
display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).
连续数字问题，多次使用相同表
select
distinct s1.*
From
stadium s1,
stadium s2,
stadium s3
where
s1.people >= 100
and s2.people >= 100
and s3.people >= 100
and
(
s1.id - s2.id = 1 and s2.id - s3.id = 1
or s1.id - s2.id = -1 and s2.id - s3.id = -1
or s1.id - s2.id = 1 and s1.id - s3.id = -1
)
order by id
- Not Boring Movies
使用匹配 NOT LIKE '%boring%'，注意与 LIKE '%[!boring]%' 的区别
select
*
From
cinema
where
id%2 != 0
and description not Like '%boring%'
order by rating Desc
- Exchange Seats **
select distinct (case
when mod(s.id,2) = 1 and s.id < t.total then s.id + 1 ## t.total不能直接写成 max(id), 因为聚合函数只会计算一次，最后出来只有一条记录，因此要把总条数单独计算出来
when mod(s.id,2) = 0 then s.id - 1
else s.id end) as id, s.student
from
seat s,
(select count(*) AS total from seat ) t # 单独计算总条数作为t
order by id
- Swap Salary
主要思想是求差集，同时替换m和f
注意
Update语句的格式
repalce的用法
update
salary
Set
sex = replace('mf',sex, '')
