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

本文总结了日期条件筛选的四种方法：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