[MySQL] --) query the troubleshooting process of cross-year issues among colleagues who celebrate their birthdays within five days _ MySQL

Preface:

Ask a friend:

SELECT * FROM ali_users where datediff (CAST (CONCAT (DATE_FORMAT (NOW (), '% Y'), DATE_FORMAT (birthday,'-% m-% d ')) as date), CAST (DATE_FORMAT (NOW (), '% y-% m-% d') as date) <= 1

1. prepare the test data, which must contain cross-year data.

1.1 prepare the SQL statement of the test data

USE test;
Drop table if exists ali_users;
Create table ali_users (username VARCHAR (10), birthday date not null, iphone VARCHAR (16 ));
Insert into ali_users SELECT \ 'maoyi \ ', \ '2017-09-04 \', \ '2017 \ 'UNION ALL
SELECT \ 'liuer \ ', \ '2014-08-30 \', \ '2014 \ 'UNION ALL
SELECT \ 'zhangsan \ ', \ '2014-01-01 \', \ '2014 \ 'UNION ALL
SELECT \ 'lisi \ ', \ '2014-01-02 \', \ '2014 \ 'UNION ALL
SELECT \ 'wangwu \ ', \ '2014-11-01 \', \ '2014 \ 'UNION ALL
SELECT \ 'zhaoliu \ ', \ '2017-11-01 \', \ '2017 \ 'UNION ALL
SELECT \ 'songqi \ ', \ '2014-08-31 \', \ '2014 \ 'UNION ALL
SELECT \ 'huangba \ ', \ '2014-09-01 \', \ '2014 \ 'UNION ALL
SELECT \ 'zengjiu \ ', \ '2014-09-02 \', \ '2014 \ 'UNION ALL
SELECT \ 'luoshi \ ', \ '2014-09-03 \', \ '2014 \ 'UNION ALL
SELECT \ 'Tom \ ', \ '2014-09-05 \', \ '2014 \ 'UNION ALL
SELECT \ 'licy \ ', \ '2014-12-30 \', \ '2014 \ 'UNION ALL
SELECT \ 'Cari \ ', \ '2017-12-31 \', \ '2017 \ 'UNION ALL
SELECT \ 'Mark \ ', \ '2014-01-03 \', \ '2014 \ 'UNION ALL
SELECT \ 'Ruby \ ', \ '2017-01-04 \', \ '2017 \';
1.2. execute SQL statements in the database command line

Mysql> USE test;
DATABASE CHANGED
Mysql> drop table if exists ali_users;
Query OK, 0 ROWS affected (0.00 sec)

Mysql> create table ali_users (username VARCHAR (10), birthday date not null, iphone VARCHAR (16 ));
Query OK, 0 ROWS affected (0.01 sec)

Mysql> insert into ali_users SELECT \ 'maoyi \ ', \ '2017-09-04 \', \ '2017 \ 'UNION ALL
-> SELECT \ 'liuer \ ', \ '2014-08-30 \', \ '2014 \ 'UNION ALL
-> SELECT \ 'zhangsan \ ', \ '2017-01-01 \', \ '2017 \ 'UNION ALL
-> SELECT \ 'lisi \ ', \ '2014-01-02 \', \ '2014 \ 'UNION ALL
-> SELECT \ 'wangwu \ ', \ '2017-11-01 \', \ '2017 \ 'UNION ALL
-> SELECT \ 'zhaoliu \ ', \ '2017-11-01 \', \ '2017 \ 'UNION ALL
-> SELECT \ 'songqi \ ', \ '2014-08-31 \', \ '2014 \ 'UNION ALL
-> SELECT \ 'huangba \ ', \ '2014-09-01 \', \ '2014 \ 'UNION ALL
-> SELECT \ 'zengjiu \ ', \ '2014-09-02 \', \ '2014 \ 'UNION ALL
-> SELECT \ 'luoshi \ ', \ '2014-09-03 \', \ '2014 \ 'UNION ALL
-> SELECT \ 'Tom \ ', \ '2014-09-05 \', \ '2014 \ 'UNION ALL
-> SELECT \ 'licy \ ', \ '2014-12-30 \', \ '2014 \ 'UNION ALL
-> SELECT \ 'Cari \ ', \ '2014-12-31 \', \ '2014 \ 'UNION ALL
-> SELECT \ 'Mark \ ', \ '2014-01-03 \', \ '2014 \ 'UNION ALL
-> SELECT \ 'Ruby \ ', \ '2017-01-04 \', \ '2017 \';
Query OK, 15 ROWS affected (0.01 sec)
Records: 15 Duplicates: 0 WARNINGS: 0

Mysql> SELECT * FROM ali_users;
+ ---------- + ------------ + ------------- +
| Username | birthday | iphone |
+ ---------- + ------------ + ------------- +
| MaoYi | 1985-09-04 | 13998786543 |
| LiuEr | 1985-08-30 | 13998786543 |
| ZhangSan | 1981-01-01 | 13998786543 |
| LiSi | 1981-01-02 | 13998786543 |
| WangWu | 1984-11-01 | 13998786543 |
| Zhaolu | 1984-11-01 | 13998786543 |
| SongQi | 1986-08-31 | 13998786543 |
| HuangBa | 1989-09-01 | 13998786543 |
| ZengJiu | 1989-09-02 | 13998786543 |
| LuoShi | 1985-0903 | 13998786543 |
| Tom | 1995-09-05 | 13998786543 |
| Licy | 1991-12-30 | 13998286543 |
| Cari | 1992-12-31 | 13998286543 |
| Mark | 13998286543 |
| Ruby | 1992-01-04 | 13998286547 |
+ ---------- + ------------ + ------------- +
15 rows in set (0.00 sec)

Mysql>
2. write the query SQL
SELECT * FROM ali_users WHERE
2, 1, cross-year problem analysis
Because the birthday field in the cross-year period is usually less than the month of January, if DATEDIFF is used to determine that the difference between the date and month is greater than that in December, the number of days is less than N days, you need YEAR (NOW () + 1, YEAR + 1 of the current YEAR plus month to compare with NOW () to get the actual days of difference.
2.2, set within 5 days
In N days, BETWEEN 0 and n is used to determine. if it is within 5 days (including today), then the value of N is 4, that is, BETWEEN 0 AND 4.
3. verify data
For example, to remind colleagues who have their birthdays in the last five days (including today), happy birthday.
3.1. the queried data is within this year. for example, if today is July 22, August 30, the SQL statement to be executed is as follows:
SELECT * FROM ali_users WHERE
The query results should be from the end of February 3 to February 3, August 30-9, including LiuEr, SongQi, HuangBa, ZengJiu, and LuoShi;

Mysql> SELECT * FROM ali_users WHERE
-> DATEDIFF (CAST (CONCAT (YEAR (NOW (), DATE_FORMAT (birthday, \ '-% m-% d \') as date ), CAST (DATE_FORMAT (NOW (), \ '% y-% m-% d \') as date) BETWEEN 0 AND 4
-> OR/* or is followed by the renewal belt to solve the cross-year problem */
-> DATEDIFF (CAST (CONCAT (YEAR (NOW () + 1, DATE_FORMAT (birthday, \ '-% m-% d \') as date ), CAST (DATE_FORMAT (NOW (), \ '% y-% m-% d \') as date) BETWEEN 0 AND 4
->;
+ ---------- + ------------ + ------------- +
| Username | birthday | iphone |
+ ---------- + ------------ + ------------- +
| LiuEr | 1985-08-30 | 13998786543 |
| SongQi | 1986-08-31 | 13998786543 |
| HuangBa | 1989-09-01 | 13998786543 |
| ZengJiu | 1989-09-02 | 13998786543 |
| LuoShi | 1985-0903 | 13998786543 |
+ ---------- + ------------ + ------------- +
5 rows in set (0.00 sec)

Mysql>
3.2, the queried birthday has a new year
For example, if we want to query my colleagues who have their birthdays within five days, we will have their birthdays on October 31, and those who have birthdays on October 2 and 3, because today is July 22, 2013, we need to change the SQL now () in Step #2 to '2017-12-30 00:10:10 'for testing. the SQL is organized as follows:
Mysql> SELECT * FROM ali_users WHERE
-> DATEDIFF (CAST (CONCAT (YEAR (\ '2017-12-30 00:10:10 \ '), DATE_FORMAT (birthday, \'-% m-% d \')) as date), CAST (DATE_FORMAT (\ '2017-12-30 00:10:10 \ ', \' % y-% m-% d \ ') as date) BETWEEN 0 AND 4
-> OR/* or is followed by the renewal belt to solve the cross-year problem */
-> DATEDIFF (CAST (CONCAT (YEAR (\ '2017-12-30 00:10:10 \ ') + 1, DATE_FORMAT (birthday, \'-% m-% d \')) as date), CAST (DATE_FORMAT (\ '2017-12-30 00:10:10 \ ', \' % y-% m-% d \ ') as date) BETWEEN 0 AND 4
->;
+ ---------- + ------------ + ------------- +
| Username | birthday | iphone |
+ ---------- + ------------ + ------------- +
| ZhangSan | 1981-01-01 | 13998786543 |
| LiSi | 1981-01-02 | 13998786543 |
| Licy | 1991-12-30 | 13998286543 |
| Cari | 1992-12-31 | 13998286543 |
| Mark | 13998286543 |
+ ---------- + ------------ + ------------- +
5 rows in set (0.00 sec)

Mysql>
4. Summary