SQL刷题笔记

2024-03-26 学习笔记 SQL 0 评论字数统计: 1.8k(字) 阅读时长: 7(分)

length和char_length函数区别

计算字段长度规则：

length是汉字是算三个字符，数字或字母算一个字符，中文标点符号(如：￥、？。，)算三个字符，英文标点符号算一个字符。
char_length是汉字、数字或字母都算是一个字符，包括中英文标点符号也算一个字符。

-- 查询某字符串长度
SELECT LENGTH("中国？￥、");  -- 15
SELECT CHAE_LENGTH("中国？￥、");  -- 5

-- 查询表中某字段数据长度最长的10条数据
SELECT * from student where length(student_name) > 0 order by length(student_name) desc limit 10;

-- 查询表中某字段数据长度最大的值
SELECT max(length(student_name)) from student;

偏移量 & 日期函数

表： Weather
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| id | int |
| recordDate | date |
| temperature | int |
±--------------±--------+
id 是该表具有唯一值的列。
没有具有相同 recordDate 的不同行。
该表包含特定日期的温度信息

编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。
返回结果无顺序要求。

偏移量解决方案

lag()和 lead()函数用于获取当前行的前一行和后一行的数据，可以通过偏移量来获取更多的行数据。
第一个为待查询的参数列名，第二个为向上[下]偏移的位数，第三个参数为超出最上面边界的默认值。
OVER子句是用于指定窗口函数操作的条件。通过ORDER BY子句，可以指定要根据哪列来排序，使得LAG()函数的结果按照指定的顺序进行计算。

日期函数解决

DATE_ADD(date,INTERVAL expr type)向日期添加指定的时间间隔。
ADDDATE()有两种形式：ADDDATE(date,INTERVAL expr unit)和 ADDDATE(expr,days)
第一种和DATE_ADD()完全一样，第二种中第二个参数只能是天。其实就是第一种的功能包括第二种。
DATEDIFF(begin,end) 函数返回begin-end的天数。
TIMESTAMPDIFF(unit,begin,end);TIMESTAMPDIFF函数返回begin-end的结果


-- 偏移
select id
from (select 
        id,
        temperature,
        recordDate,
        lag(recordDate,1) over(order by recordDate) as last_date,
        lag(temperature,1) over(order by recordDate) as last_temperature
    from Weather
) a
where temperature>last_temperature and DATEDIFF(recordDate,last_date)=1

-- 日期函数
select a.id
from weather a 
join weather b 
on (a.recorddate = adddate(b.recorddate,INTERVAL 1 day))
-- on (a.recorddate = adddate(b.recorddate,1))
-- on (a.recorddate = date_add(b.recorddate,INTERVAL 1 day))
where a.temperature > b.temperature 

-- 最快
select a.id 
from weather a
join weather b
where DATEDIFF(a.recorddate,b.recorddate)=1
and a.temperature>b.temperature

-- 
select a.id
from weather a,weather b
where DATEDIFF(a.recorddate,b.recorddate)=1 and a.Temperature >b.Temperature

JOIN方式

博客链接

Employee

empId	name	supervisor	salary
3	Brad	null	4000
1	John	3	1000
2	Dan	3	2000
4	Thomas	3	4000

bouns

empId	bonus
2	500
4	2000
5	200

JOIN方式默认inner JOIN

select *
from employee
[inner] JOIN bonus on employee.empId = bonus.empId

empId	name	supervisor	salary	empId	bonus
2	Dan	3	2000	2	500
4	Thomas	3	4000	4	2000

LEFT JOIN方式

select *
from employee
left JOIN bonus on employee.empId = bonus.empId

empId	name	supervisor	salary	empId	bonus
3	Brad	null	4000	null	null
1	John	3	1000	null	null
2	Dan	3	2000	2	500
4	Thomas	3	4000	4	2000

RIGHT JOIN方式

1
2
3

select *
from employee
right JOIN bonus on employee.empId = bonus.empId

empId	name	supervisor	salary	empId	bonus
2	Dan	3	2000	2	500
4	Thomas	3	4000	4	2000
null	null	null	null	5	200

FULL JOIN方式

-- mysql中未提供full join 使用左查询 union 右查询 实现
select *
from employee
left JOIN employeebonus on employee.empId = employeebonus.empId
UNION
select *
from employee
right JOIN employeebonus on employee.empId = employeebonus.empId

empId	name	supervisor	salary	empId	bonus
3	Brad	null	4000	null	null
1	John	3	1000	null	null
2	Dan	3	2000	2	500
4	Thomas	3	4000	4	2000
null	null	null	null	5	200

窗口函数

Delivery 表：
±------------±------------±-----------±----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
±------------±------------±-----------±----------------------------+
| 1 | 1 | 2019-08-01 | 2019-08-02 |
| 2 | 2 | 2019-08-02 | 2019-08-02 |
| 3 | 1 | 2019-08-11 | 2019-08-12 |
| 4 | 3 | 2019-08-24 | 2019-08-24 |
| 5 | 3 | 2019-08-21 | 2019-08-22 |
| 6 | 2 | 2019-08-11 | 2019-08-13 |
| 7 | 4 | 2019-08-09 | 2019-08-09 |
±------------±------------±-----------±----------------------------+

delivery_id 是该表中具有唯一值的列。
该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）。

如果顾客期望的配送日期和下单日期相同，则该订单称为「即时订单」，否则称为「计划订单」。
「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。
编写解决方案以获取即时订单在所有用户的首次订单中的比例。保留两位小数。

ROW_NUMBER分组排序

ROW_NUMBER()分组排序后给组内数据一个顺序编号,可用来取前几

TO_DAYS()判断日期是否是同一天

TO_DAYS()返回日期和年份0之间的天数

SELECT 
    Round(sum(if(TO_DAYS(order_date)=TO_DAYS(customer_pref_delivery_date),100,0))/count(*),2) as immediate_percentage 
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY customer_id   ORDER BY order_date ) AS row_num
    FROM Delivery 
) AS ranked
WHERE row_num = 1

当表格为空时，返回null值

MyNumbers 表：
±------------±-----+
| Column Name | Type |
±------------±-----+
| num | int |
±------------±-----+

该表可能包含重复项（换句话说，在SQL中，该表没有主键）。
这张表的每一行都含有一个整数。

单一数字是在 MyNumbers 表中只出现一次的数字。
找出最大的单一数字。如果不存在单一数字，则返回 null 。
查询结果如下例所示。

-- 1.可以使用聚合函数进行空值null值的转换，具体的聚合函数包括SUM/AVG/MAX/MIN
select max(a.num) num
from (
    select num
from mynumbers
group by num
having count(num)=1
order by num DESC
limit 1
) a


-- 2.可以使用select语句进行转换，但空值应直接写在select中而非from中
select (select num
from mynumbers
group by num
having count(num)=1
order by num DESC
limit 1) num

-- 3.使用ifnull，ifnull并没有起作用，这种实现类似第二种
select ifnull((select max(num)
from mynumbers
group by num
having count(*) = 1
Order by num DESC
limit 1), null) num

分条件查询

产品数据表: Products

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| product_id | int |
| new_price | int |
| change_date | date |
±--------------±--------+

(product_id, change_date) 是此表的主键（具有唯一值的列组合）。
这张表的每一行分别记录了某产品在某个日期更改后的新价格。
编写一个解决方案，找出在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。

-- 1.找出所有的产品：
select distinct product_id from products 
-- 2.找到 2019-08-16 前所有有改动的产品的最新价格。
-- 2.1使用 max 函数找到产品最新修改的时间。使用 where 查询限制时间小于等于 2019-08-16：
select product_id, max(change_date)
from products
where change_date <= '2019-08-16'
group by product_id

-- 2.2使用 where 子查询，根据 product_id 和 change_date 找到对应的价格：
select product_id, new_price 
from products
where (product_id, change_date) in (
    select product_id, max(change_date)
    from products
    where change_date <= '2019-08-16'
    group by product_id
)
-- 3.上面两步已经找到了所有的产品和已经修改过价格的产品。使用 left join 得到所有产品的最新价格，如果没有设置为 10。

select p1.product_id, ifnull(p2.new_price, 10) as price --ifnull用来给未修改过的商品价格
from (
    select distinct product_id
    from products
) as p1 -- 所有的产品
left join (
    select product_id, new_price 
    from products
    where (product_id, change_date) in (
        select product_id, max(change_date)
        from products
        where change_date <= '2019-08-16'
        group by product_id
    )
) as p2 -- 在 2019-08-16 之前有过修改的产品和最新的价格
on p1.product_id = p2.product_id

本文链接： https://gladdduck.github.io/2024/03/26/实习-SQL刷题记录/

版权声明： 本博客所有文章除特别声明外，均采用 CC BY 4.0 CN协议许可协议。转载请注明出处！

GladdduckKB Master

我写的个人简介。