力扣数据库题

175. 组合两个表

'''
查询Person 表中每个人的姓、名、城市和州，如果 personId 的地址不在 Address 表中，则报告为空 null 。
'''
SELECT Person.lastname, Person.firstname, Address.city, Address.state
FROM Person 
LEFT JOIN Address 
ON Person.personId = Address.personId  #USING(personId)

注释：A INEER JOIN B ON 关联查询的条件                        取交集。

           A LEFT JOIN B  ON 关联查询的条件                         取 A 全部,B 没有对应的值为 null。

           A RIGHT JOIN B ON 关联查询的条件                       取 B 全部, A 没有对应的值为 null。

           A FULL OUTER JOIN  B  ON 关联查询的条件件      取并集，彼此没有对应的值为 null

          (MYSQL不支持外联 想要实现这个的话只能UNION左右)

          USING: USING(id)等价于JION ON a.id=b.id

#并联两个表全部信息
SELECT Person.lastname, Person.firstname, Address.city, Address.state 
FROM Person 
LEFT JOIN Address ON Person.personId = Address.personId 
UNION
SELECT Person.lastname, Person.firstname, Address.city, Address.state 
FROM Person 
RIGHT JOIN Address ON Person.personId = Address.personId 

     

176. 第二高的薪水

#解法一
#将不同的薪资按降序排序，然后使用 LIMIT 子句获得第二高的薪资
SELECT DISTINCT salary AS SecondHighestSalary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1 
'''
LIMIT和OFFSET连用，LIMIT Y OFFET X 等价于 X,Y 跳过X条取Y条数据
'''
 
 
 
'''
然而，如果没有这样的第二最高工资，这个解决方案将被判断为 “错误答案”，因为本表可能只有一项记录。为了克服这个问题，我们可以将其作为临时表
附加一个嵌套查询，内层查询结果为为空时，将 Null 附给 SecondHighestSalary 使得其返回值为 Null
'''
SELECT
(SELECT DISTINCT FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1 )
AS SecondHighestSalary
 
 
 
 
#解法二 去除最高薪资后的最高薪资
SELECT max(salary) AS SecondHighestSalary 
FROM Employee 
WHERE salary < (SELECT max(salary) FROM Employee)

178. 分数排名

最新版本MYSQL8已经支持了排名函数RANK，DENSE_RANK和ROW_NUMBER

RANK：并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到               总共的排名。（1，1，3）
DENSE_RANK：并列连续排序，并列即相同的值，相同的值保留重复名次，遇到下一个不同值                                时，依然按照连续数字排名。（1，1，2）
ROW_NUMBER：连续排名，即使相同的值，依旧按照连续数字进行排名。（1，2，3）

#分数排序，相同分数排名相同，下一个分数排名数字连续
SELECT score, DENSE_RANK() OVER(ORDER BY score DESC) 'rank'
FROM Scores

610. 判断三角形

新增一列判断给定的x,y,z是否可以组成三角形

select *,
if(
  x+y>z and x+z>y and y+z>x,
  "Yes",
  "No"
)triangle    #新增的一列
from triangle

196. 删除重复的电子邮箱

DELETE FROM Person WHERE id not in(
SELECT id FROM (SELECT min(id) AS id FROM Person GROUP BY email) AS T
)

• 先用group by 对email进行去重， 保留最小的id
• 再delete from table where id not in (保留的最小id)

197. 上升的温度（查找今天比昨天温度高的所有日期）

• 怎么判断是相邻两天？
  • DATEDIFF函数 DATEDIFF('2019-01-02'，'2019-01-01'）=1，DATEDIFF(今天，前天）=2
  • TIMESTAMPDIFF(day/hour/second, '2019-01-02'，'2019-01-01') = -1 和上面正负号相反
• 怎么比较今天昨天温度？
  • 笛卡尔积，两边交叉连接，比较两表的温度

SELECT w1.id
FROM Weather w1 
CROSS JOIN Weather w2 
ON DATEDIFF(w1.recordDate, w2.recordDate)=1 AND w1.Temperature > w2.Temperature 

511. 游戏玩法分析 I(每位玩家 第一次登陆平台的日期)

        日期也可以min（）

584. 寻找用户推荐人

MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN

任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。这个“任何值”包括 NULL 本身！这就是为什么 MySQL 提供 IS NULL 和 IS NOT NULL 两种操作来对 NULL 特殊判断。

NULL不是值，所以不能对其使用谓词，如果使用谓词，结果是unknown。 可以认为它只是一个没有值的标记,而比较谓词只适用于比较值。因此对非值的NULL使用比较谓词是没有意义的

SELECT name FROM customer WHERE referee_id <> 2 OR referee_id IS NULL;

627.变更性别

表中的男女性别交换（交换f m）

update Salary SET sex = char(ASCII('f')+ASCII('m')-ASCII(sex))
#太牛了 A ASCII码 65 a 97 0 48