85 lines
2.1 KiB
Markdown
85 lines
2.1 KiB
Markdown
|
# MySQL 中按年、季度、月、周、天进行查询
|
|||
|
|
|
|||
|
|
**1、查询当天的数据**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM <table_name> WHERE TO_DAYS(datetime_col) = TO_DAYS(NOW());
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**2、查询本周数据 (上周日到本周六)**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM <table_name> WHERE YEARWEEK(DATE_FORMAT(<datetime_col>, '%Y-%m-%d' )) = YEARWEEK(NOW());
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**3、 查询近 7 天的数据, 不包括当天**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM <table_name> WHERE TO_DAYS(NOW()) - TO_DAYS(<datetime_col>) <= '7' AND TO_DAYS(NOW()) - TO_DAYS(<datetime_col>) > 0;
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**4、 查上周的数据**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM <table_name> WHERE YEARWEEK(date_format(<datetime_col>, '%Y-%m-%d')) = YEARWEEK(now())- 1;
|
|||
|
|
|
|||
|
|
-- 或
|
|||
|
|
|
|||
|
|
SELECT * FROM <table_name> WHERE date_format(<datetime_col>, '%Y-%m')= date_format(DATE_SUB(curdate(), INTERVAL 1 WEEK), '%Y-%m');
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**5、查询本月的数据**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM <table_name> WHERE DATE_FORMAT(<datetime_col>, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**6、查询上个月的数据**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM <table_name> WHERE date_format(<datetime_col>, '%Y-%m')= date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH), '%Y-%m');
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**7、查询近 30 天的数据**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM <table_name> WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(<datetime_col>);
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**8、查询某个季度（所有这个季度）的数据**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM <table_name> WHERE QUARTER(<datetime_col>) = QUARTER(NOW());
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**9、查询本年本季度的数据**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM <table_name> WHERE QUARTER(<datetime_col>) = QUARTER(NOW()) and YEAR(<datetime_col>) = YEAR(NOW());
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**10、查询上季度的数据**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM <table_name> WHERE QUARTER(<datetime_col>)+4*YEAR(<datetime_col>) = YEAR(NOW())+QUARTER(NOW())-1;
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**11、查询距现在 6 个月的数据**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM <table_name> WHERE <datetime_col> BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**12、查询本年的数据**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM log WHERE YEAR(<datetime_col>) = YEAR(NOW());
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**13、查询上年的数据**
|
|||
|
|
|
|||
|
|
```mysql
|
|||
|
|
SELECT * FROM log WHERE YEAR(<datetime_col>) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));
|
|||
|
|
```
|
|||
|
|
|