180. 连续出现的数字
编写一个 SQL 查询,查找Logs 表中所有至少连续出现三次的数字。返回的结果表中的数据可以按 任意顺序 排列。
Logs 表:
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
想到的方法可以说是非常的朴实无华,但是官方的解居然也是这样写的…
select distinct l1.Num 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 l1.Num = l3.Num
184. 部门工资最高的员工
编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
Employee 表:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
联表查询的题,一眼join。
但此题的特殊之处还在where (xx,xx) in (...)
这句。
它给了我们sql语句写法的新思路。
select
d.name Department,
Employee.name Employee,
Salary
from Employee
join Department d
on Employee.departmentId =d.id
where (Employee.DepartmentId,Salary) in (
select DepartmentId, MAX(Salary)
from Employee
group by departmentId
)
626. 换座位
编写SQL查询以查找每个部门中薪资最高的员工。按 任意顺序 返回结果表。
Employee 表:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
最先想到的直接、粗暴,且诡异的写法
select s1.id, s2.student
from Seat s1,Seat s2
where (s1.id=s2.id-1 and s1.id%2=1)
or (s1.id=s2.id+1 and s1.id%2=0)
or (s1.id=(select max(id) from Seat) and
s2.id=s1.id and s1.id%2=1)
order by s1.id
聪明的写法:
COALESCE(x,y) 函数:选出x,y中不是null的那个。
x ^ 1:按位异或(Xor)
e.g.
111^1=000
101^1=010
SELECT
s1.id, COALESCE(s2.student, s1.student) AS student
FROM
seat s1
LEFT JOIN
seat s2 ON ((s1.id + 1) ^ 1) - 1 = s2.id
ORDER BY s1.id;
1158. 市场分析 I
请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。
select
u.user_id as buyer_id,
u.join_date,
count(o.order_id) as orders_in_2019
from users u
left join orders o
on u.user_id = o.buyer_id
and year(o.order_date) = '2019'
group by u.user_id;
1393. 股票的资本损益
两种写法:
第一种:CASE ... END ...
CASE input_expression
WHEN expression1 THEN result_expression1
WHEN expression2 THEN result_expression2
[...n]
ELSE result_expression
END
SUM(
CASE operation
WHEN 'sell'
THEN price ELSE -price
END
) AS capital_gain_loss
第二种:if(表达式, 若成立则为此数, 若不成立则此数)
select
stock_name,
sum(
if(operation = 'buy', -price, price)
) as capital_gain_loss
from Stocks
group by stock_name