MYSQL四种筛选时间日期条件的方法

2024-01-05 19:53| 来源: 网络整理| 查看: 265

本文总结了日期条件筛选的四种方法：year()&month()，date_format()，like 和left()。同样的方式可以应用到时间的条件筛选题目中。

例题

写一个 SQL 语句, 报告消费者的 id 和名字, 其中消费者在 2020 年 6 月和 7 月, 每月至少花费了$100.

结果表无顺序要求.

Customers +--------------+-----------+-------------+ | customer_id | name | country | +--------------+-----------+-------------+ | 1 | Winston | USA | | 2 | Jonathan | Peru | | 3 | Moustafa | Egypt | +--------------+-----------+-------------+ Product +--------------+-------------+-------------+ | product_id | description | price | +--------------+-------------+-------------+ | 10 | LC Phone | 300 | | 20 | LC T-Shirt | 10 | | 30 | LC Book | 45 | | 40 | LC Keychain | 2 | +--------------+-------------+-------------+ Orders +--------------+-------------+-------------+-------------+-----------+ | order_id | customer_id | product_id | order_date | quantity | +--------------+-------------+-------------+-------------+-----------+ | 1 | 1 | 10 | 2020-06-10 | 1 | | 2 | 1 | 20 | 2020-07-01 | 1 | | 3 | 1 | 30 | 2020-07-08 | 2 | | 4 | 2 | 10 | 2020-06-15 | 2 | | 5 | 2 | 40 | 2020-07-01 | 10 | | 6 | 3 | 20 | 2020-06-24 | 2 | | 7 | 3 | 30 | 2020-06-25 | 2 | | 9 | 3 | 30 | 2020-05-08 | 3 | +--------------+-------------+-------------+-------------+-----------+ Result 表: +--------------+------------+ | customer_id | name | +--------------+------------+ | 1 | Winston | +--------------+------------+ Winston 在2020年6月花费了$300(300 * 1), 在7月花费了$100(10 * 1 + 45 * 2). Jonathan 在2020年6月花费了$600(300 * 2), 在7月花费了$20(2 * 10). Moustafa 在2020年6月花费了$110 (10 * 2 + 45 * 2), 在7月花费了$0. 答案整理解法一：Year ()和Month ()

因为where后面有多重条件，所以需要注意SQL where条件的执行顺序是：（）、not、and、or。这里建议将 month(order_date) = 7 or month(order_date)=6 用括号括起来，表示是2020年，6月或7月。

select a.customer_id ,name from orders a left join product b on a.product_id = b.product_id left join customers c on a.customer_id = c.customer_id where YEAR(order_date) = 2020 and (month(order_date) = 7 or month(order_date)=6) group by customer_id having sum(case when YEAR(order_date) = 2020 and month(order_date) = 7 then quantity*price else 0 end )>=100 and sum(case when YEAR(order_date) = 2020 and month(order_date) = 6 then quantity*price else 0 end )>=100 解法二：date_format ()

使用date_format()函数转换日期格式，提取所需要的年月信息。 date_format的用法在以前的笔记中有总结，这里就不多赘述。

select a.customer_id ,name from orders a left join product b on a.product_id = b.product_id left join customers c on a.customer_id = c.customer_id where date_format(order_date,'%Y%m')in ('202006','202007') group by customer_id having sum(case when date_format(order_date,'%Y%m')= '202006' then quantity*price else 0 end )>=100 and sum(case when date_format(order_date,'%Y%m')= '202007' then quantity*price else 0 end )>=100 解法三：Like

逻辑和上面的date_format()一样，这里我们将函数换成了like和通配符%。

like用法笔记：

通配符描述%替代 0 个或多个字符_（下划线）任何单个字符。 select a.customer_id ,name from orders a left join product b on a.product_id = b.product_id left join customers c on a.customer_id = c.customer_id where order_date like "2020-06%" or "2020-07%" group by customer_id having sum(case when order_date like "2020-06%" then quantity*price else 0 end) >= 100 and sum(case when order_date like "2020-07%" then quantity*price else 0 end) >= 100 解法三：Left ()

left()是取从左往右数的第n个的字符串。

having sum(case when left(order_date,7)='2020-06' then quantity*price else 0 end)>=100 and sum(case when left(order_date,7)='2020-07' then quantity*price else 0 end)>=100

https://leetcode-cn.com/problems/customer-order-frequency

【本文地址】

公司简介

联系我们