MYSQL：你能取出匹配3个以上表达式的结果吗？

您可以使用CASE语句获取与某些表达式匹配的结果−

SELECT * FROM yourTableName
WHERE CASE WHEN yourColumnName1 = yourValue1 THEN 1 ELSE 0 END +
   CASE WHEN yourColumnName2 = yourValue2 THEN 1 ELSE 0 END +
   CASE WHEN yourColumnName3 = yourValue3 THEN 1 ELSE 0 END +
   .
   .
CASE WHEN yourColumnNameN = yourValueN THEN 1 ELSE 0 END > = 3;

为了理解上面的语法，让我们创建一个表。创建表的查询如下 –

mysql> CREATE TABLE UserInformation
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> FirstName varchar(20),
   -> LastName varchar(20),
   -> Age int,
   -> Marks int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.58 sec)

使用插入命令在表中插入一些记录。查询如下 –

mysql> INSERT INTO UserInformation(FirstName,LastName,Age,Marks) VALUES('John','Smith',22,89);
Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO UserInformation(FirstName,LastName,Age,Marks) VALUES('Carol','Taylor',21,80);
Query OK, 1 row affected (0.19 sec)

mysql> INSERT INTO UserInformation(FirstName,LastName,Age,Marks) VALUES('John','Doe',24,81);
Query OK, 1 row affected (0.14 sec)

mysql> INSERT INTO UserInformation(FirstName,LastName,Age,Marks) VALUES('David','Miller',29,99);
Query OK, 1 row affected (0.15 sec)

mysql> INSERT INTO UserInformation(FirstName,LastName,Age,Marks)
VALUES('Mitchell','Johnson',22,65);
Query OK, 1 row affected (0.13 sec)

使用SELECT语句显示表中的所有记录。查询如下 –

mysql> SELECT * FROM UserInformation;

以下是输出。

+----+-----------+----------+------+-------+
| Id | FirstName | LastName | Age  | Marks |
+----+-----------+----------+------+-------+
|  1 | John      | Smith    |   22 | 89    |
|  2 | Carol     | Taylor   | 21   | 80    |
|  3 | John      | Doe      | 24   | 81    |
|  4 | David     | Miller   | 29   | 99    |
|  5 | Mitchell  | Johnson  | 22   | 65    |
+----+-----------+----------+------+-------+
5 rows in set (0.00 sec)

这里是取出与部分(不是全部)表达式匹配的记录的查询。查询如下 –

mysql> SELECT * FROM UserInformation
   -> WHERE CASE WHEN FirstName = 'Mitchell' THEN 1 ELSE 0 END +
   -> CASE WHEN LastName = 'Johnson' THEN 1 ELSE 0 END +
   -> CASE WHEN Age = 22 THEN 1 ELSE 0 END +
   -> CASE WHEN Marks = 67 THEN 1 ELSE 0 END > = 3;

以下是输出 –

+----+-----------+----------+------+-------+
| Id | FirstName | LastName | Age  | Marks |
+----+-----------+----------+------+-------+
|  5 | Mitchell  | Johnson  | 22   | 65    |
+----+-----------+----------+------+-------+
1 行记录(0.00 秒)

阅读更多：MySQL 教程