Mysql 刷题Day09

LC 585 2016年的投资

思路：

本题思路好想 ， 就是把2015年投资相同的找出来 ，再找出这其中经纬度不同的id对应的2016年的保险。

实际操作中， 发现用group by很麻烦， 那么想到窗口函数也能 分组进行统计

利用 count() over (partition by)可以分组统计的同时 ，不合并原始行 ， 可以很方便的查找出符合条件的行。

with temp as(
select Insurance.pid ,
tiv_2016,
count(*) over(partition by tiv_2015) cnt ,
count(*) over(partition by lat , lon) cnt2
from Insurance)select round(sum(tiv_2016) , 2) as tiv_2016 from temp
where cnt > 1 and cnt2 = 1

知识点：

1.partition by 相关语法 

count(*)  over(partition by column1[column2]) 

和group by 一样 ，partition by 后面可以加多个字段 ，这些字段全都相同的才视为同一组。

2.group by相关语法

一开始用group by ， 写出这样的语句

select pid from Insurance
group by tiv_2015 having count(tiv_2015) > 1

以为可以从这张表中找出 pid为1，3，4

但事实上，

• ​​GROUP BY tiv_2015​​：将数据按 tiv_2015 分组，得到以下分组：
  • tiv_2015 = 10：包含 pid = 1, 3, 4。
  • tiv_2015 = 20：包含 pid = 2。
• ​​HAVING COUNT(...) > 1​​：保留重复的 tiv_2015 分组（即 tiv_2015 = 10）。
• ​​SELECT pid​​：由于 pid 不在 GROUP BY 中且非聚合列，数据库会从 tiv_2015 = 10 的分组中 ​​随机选择一个 pid​​（如 1）。

根本原因在于 select的列必须在group by的列中 ， 或者跟着聚合函数才行！

LC 185 部门工资前三高的员工

知识点

和排名相关的窗口函数

1.ROW_NUMBER()

ROW_NUMBER() OVER (ORDER BY column [ASC|DESC])

它会为每一行分配连续的序号，不管值相不相同.

2.rank()

为每一行分配排名，相同值共享同一排名，后续排名跳过重复位次。

3.dense_rank()

为每一行分配排名，相同值共享同一排名，后续排名不跳位。

注意，所有和排序相关的窗口函数 ， 都必须 跟 order by 

这几种排序的窗口函数 ， 符合题意得是第三种，代码如下：

with temp as 
(select d.name Department ,e.name Employee , salary, 
dense_rank() over(partition by d.id order by salary desc )  rk 
from Employee e inner join Department d on e.departmentId = d.id)select Department , Employee , salary as Salary  from temp
where rk <= 3

LC1527 患某种疾病的患者

知识点：like的相关语法

like用于mysql中字符串的模糊匹配 ，

1. LIKE 'abc%' 

表示 以abc开头的字符串 ，%为通配占位符

2.LIKE '%abc%'

表示包含了abc的字符串

3.LIKE '%abc' 

表示包含以abc结尾的字符串。

记忆方法在于：%在哪边 ， 表示哪边可以有其他的字符串内容

4. _ 占位符

用于匹配单个字符

-- 匹配第2个字符为 "a" 的字符串（如 "ba", "cat"）
WHERE column LIKE '_a%';-- 匹配长度为3且第3个字符为 "x" 的字符串（如 "abx", "1x2"）
WHERE column LIKE '__x';

本体思路：

一个坑点在于， 糖尿病的字符串必须以 “DIAB1”开头 ，SADIAB100并不是糖尿病

那么， 如何判断给出的condition中包不包含“DIAB1”呢？

这里就用到like了 ， 

分两种情况判断 ，

1.DIAB1位于condition的开头 ， 那么用 like 'DIAB1%'即可

2.DIAB1位于condition的中间， 而题目明确指出 ， condition字符串中， 不同病症用空格分隔，

那么 要判断DIAB1是否包含在字符串中 ， 就是判断  ' DIAB1'（DIAB1加了个空格）是否完整出现

使用like '% DIAB1'

select patient_id , patient_name ,  conditions
from Patients
where conditions like 'DIAB1%' or conditions like '% DIAB1%'