LeetCode数据库sql相关题

简单题

leetcode上的数据库题目都不太简单，下面总结一些小技巧共勉

数据库函数

197.上升的温度

datediff(day1, day2) 
# 比较日期

# 完整答案
select w1.id from Weather w1, Weather w2 where datediff(w1.recordDate, w2.recordDate) = 1 and w1.Temperature > w2.Temperature;

511 游戏玩法分析

MIN()
# 完整答案
# Write your MySQL query statement below
SELECT
    player_id,
    MIN(event_date) first_login
FROM Activity
GROUP BY player_id;

round函数，设置精确度
1075.项目员工

select 
    p.project_id as project_id, 
    round(sum(experience_years)/count(1),2) as average_years
    from Project p 
        left join Employee e 
        on p.employee_id = e.employee_id 
        group by p. project_id; 

1211.查询结果的质量和占比
评论区大佬们真是,respect

select 
    query_name, 
    round(avg(rating/position), 2) quality,
    round(avg(rating < 3) * 100 , 2) poor_query_percentage
from Queries
group by query_name;

group_concat函数
1484.按日期分组销售产品

select 
    sell_date, 
    # 获取“不同的”产品数【count(distinct product)】
    count(distinct product) as num_sold, 
    # “不同的”【distinct product】产品按照字典排序【order by product】  & “,”分割【separator ','】
    group_concat(distinct product order by product separator ',') as products
from Activities
group by sell_date
order by sell_date;

字符串相关函数
1667.修复表中的名字

# Write your MySQL query statement below
# 一、计算字段

# 其实本题主要考察的就是计算字段的使用。
# 二、知识点
# 2.1 CONCAT() 函数

# CONCAT 可以将多个字符串拼接在一起。
# 2.2 LEFT(str, length) 函数

# 从左开始截取字符串，length 是截取的长度。
# 2.3 UPPER(str) 与 LOWER(str)

# UPPER(str) 将字符串中所有字符转为大写

# LOWER(str) 将字符串中所有字符转为小写
# 2.4 SUBSTRING(str, begin, end)

# 截取字符串，end 不写默认为空。

# SUBSTRING(name, 2) 从第二个截取到末尾，注意并不是下标，就是第二个。

# CONCAT 用来拼接字符串 ● LEFT 从左边截取字符 ● RIGHT 从右边截取字符 ● UPPER 变为大写 ● LOWER 变为小写 ● LENGTH 获取字符串长度

# select user_id, CONCAT(UPPER(left(name, 1)), LOWER(SUBSTRING(name, 2))) as name
select user_id, CONCAT(UPPER(left(name, 1)), LOWER(RIGHT(name, length(name) - 1))) as name
from Users
order by user_id

字符串操作
1683.无效推文

# 答案
select tweet_id
from Tweets
where char_length(content) > 15;

1、char_length(str)
（1）计算单位：字符
（2）不管汉字还是数字或者是字母都算是一个字符
2、length(str)
（1）计算单位：字节
（2）utf8编码：一个汉字三个字节，一个数字或字母一个字节。
（3）gbk编码：一个汉字两个字节，一个数字或字母一个字节。

技巧

1.自己连接自己，自连接
181.超过经理收入的员工
183.从不订购的客户
2. = 或 != 只能判断基本数据类型is关键字只能判断null <=>既能判断null，又能判断基本数据类型
584.寻找用户推荐人https://leetcode.cn/problems/find-customer-referee/comments/)
3.联合查询union会自动去重
1789.员工的直属部门

select employee_id, department_id from Employee
group by 1 having count(1) = 1
union
select employee_id,department_id from Employee
where primary_flag = 'Y'

4.列变行，行变列
1795.每个产品在不同商店的价格
答案出处
列变行

SELECT product_id, 'store1' store, store1 price FROM products WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2' store, store2 price FROM products WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3' store, store3 price FROM products WHERE store3 IS NOT NULL;

行变列

SELECT 
  product_id,
  SUM(IF(store = 'store1', price, NULL)) 'store1',
  SUM(IF(store = 'store2', price, NULL)) 'store2',
  SUM(IF(store = 'store3', price, NULL)) 'store3' 
FROM
  Products1 
GROUP BY product_id ;

我觉得需要注意的语法

贴一个我觉得比较全的语法总结
1.子查询的表格需要自定义名字
596.超过5名学生的课

select class from (select class, count(DISTINCT student) as num from Courses GROUP by class) as temp
where num >= 5 ;

select max(num) as num from (select num from MyNumbers group by num having count(num) = 1) as temp; 

3.case when的使用
1407.排名靠前的旅行者

select 
    u.name,
    sum(case 
        when r.distance is null then 0
        when r.distance is not null then r.distance
        end ) as travelled_distance
from
    Users u left join Rides r
    on u.id = r.user_id
group by  u.id
order by 
    travelled_distance desc, name asc;

正则表达式
1517.查找拥有有效邮箱的用户

select 
    user_id,name,mail 
from users 
where mail 
    regexp '^[a-zA-Z][0-9a-zA-Z\-\_\\.]*@leetcode\\.com$'

接触不多的语法

610.判断三角形
嵌入case语句

select x,y,z, CASE
            when x+y>z and x+z>y and y+z>x then 'Yes'
            else 'No'
        end as 'triangle'
from
    Triangle;
# update语句中的case语句
update salary set sex = 
    case sex when 'f' then 'm'
    else 'f' end;

简单题里的卧龙凤雏
1179.重新格式化部门表
这道题虽然在简单题里，但是还是有一定的难度，暂时没有官方题解，大家可以看一看各方大佬的答案。
贴一个评论区大佬的答案(这是简单题？

SELECT id, 
SUM(CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue,
SUM(CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue,
SUM(CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue,
SUM(CASE WHEN month='Apr' THEN revenue END) AS Apr_Revenue,
SUM(CASE WHEN month='May' THEN revenue END) AS May_Revenue,
SUM(CASE WHEN month='Jun' THEN revenue END) AS Jun_Revenue,
SUM(CASE WHEN month='Jul' THEN revenue END) AS Jul_Revenue,
SUM(CASE WHEN month='Aug' THEN revenue END) AS Aug_Revenue,
SUM(CASE WHEN month='Sep' THEN revenue END) AS Sep_Revenue,
SUM(CASE WHEN month='Oct' THEN revenue END) AS Oct_Revenue,
SUM(CASE WHEN month='Nov' THEN revenue END) AS Nov_Revenue,
SUM(CASE WHEN month='Dec' THEN revenue END) AS Dec_Revenue
FROM department
GROUP BY id
ORDER BY id;

可以直接在select之后写查询语句

select 
    contest_id, 
    round(count(distinct user_id)/(select count(user_id) from Users)*100, 2) as percentage
from Register
group by contest_id
order by percentage desc, contest_id asc;