使用JOIN使用AND选择具有多个条件的记录吗？
让我们首先创建一个演示表-
mysql> create table selectPerson -> ( -> PersonId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> PersonName varchar(20), -> PersonFavouriteFruit varchar(60) -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('John','Banana');
mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('John','Blackberry');
mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('John','Blueberry');
mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Carol','Apple');
mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Carol','Avocado');
mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Carol','Banana');
mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Sam','Ackee');
mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Sam','Apple');
mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Sam','Apricots');
mysql> insert into selectPerson(PersonName,PersonFavouriteFruit) values('Sam','Banana');使用select语句显示表中的所有记录。查询如下-
mysql> select *from selectPerson;
这是输出-
+----------+------------+----------------------+ | PersonId | PersonName | PersonFavouriteFruit | +----------+------------+----------------------+ | 1 | John | Banana | | 2 | John | Blackberry | | 3 | John | Blueberry | | 4 | Carol | Apple | | 5 | Carol | Avocado | | 6 | Carol | Banana | | 7 | Sam | Ackee | | 8 | Sam | Apple | | 9 | Sam | Apricots | | 10 | Sam | Banana | +----------+------------+----------------------+ 10 rows in set (0.00 sec)
以下是查询来选择同时喜欢Apple和Banana的人-
mysql> SELECT tbl1.PersonName -> FROM selectPerson tbl1 JOIN selectPerson tbl2 on tbl1.PersonName =tbl2.PersonName -> WHERE -> tbl1.PersonFavouriteFruit='Banana' -> and -> tbl2.PersonFavouriteFruit='Apple';
以下是输出-
+------------+ | PersonName | +------------+ | Carol | | Sam | +------------+ 2 rows in set (0.00 sec)