Leetcode 数据库中等题（day 1）

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