MySQL table join itself

MySQL Databases can not only be joined between tables, but also can be joined by themselves. This function is quite comfortable. Let's take a look at how to make the table join itself in mysql.

Create a table in MySQL
Create table d
(
Id int (10) auto_increment primary key not null,
Name char (20) not null,
Sex varchar (10) not null,
Birth date default null
) Type = myisam default charset = gbk;

Now we insert some data to table d.
Insert into 'D' ('id', 'name', 'sex', 'birth') VALUES
(1, 'gwen ', 'M', '2017-03-17 '),
(2, 'harold ', 'F', '2017-05-13 '),
(3, 'fang ', 'M', '2017-08-27 '),
(4, 'iane ', 'M', '2017-08-31 '),
(5, 'gwens', 'F', '2017-09-11 '),
(6, 'wen', 'F', '2017-08-03 ');

In table d, we need to find out the opposite sex and the age gap is no more than 4 years old. here we need to use table join:
The statement is as follows:
Select p1.name, p2.name, p1.sex, p2.sex, p1.birth, p2.birth from d as p1, d as p2 where p1.sex = 'F' and p2.sex = 'M' and (year (p1.birth) -year (p2.birth)-(right (p1.birth, 5) <right (p2.birth, 5) <4 or (year (p1.birth)-year (p2.birth )) -(right (p1.birth, 5) <right (p2.birth, 5)>-4 );
The specific syntax can be found in the mysql manual, which is detailed.
The result is:

 
Name sex birth
Harold Gwen f m 1989-05-13 1989-03-17
Gwens Gwen f m 1988-09-11 1989-03-17
Wen Gwen f m 1988-08-03 1989-03-17
Harold Fang f m 1989-05-13 1990-08-27
Gwens Fang f m 1988-09-11 1990-08-27
Wen Fang f m 1988-08-03 1990-08-27
Harold iane f m 1989-05-13 1979-08-31
Gwens iane f m 1988-09-11 1979-08-31
Wen iane f m 1988-08-03 1979-08-31