MYSQL：您可以从4个表达式中提取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) -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into UserInformation(FirstName,LastName,Age,Marks) values('John','Smith',22,89);
mysql> insert into UserInformation(FirstName,LastName,Age,Marks) values('Carol','Taylor',21,80);
mysql> insert into UserInformation(FirstName,LastName,Age,Marks) values('John','Doe',24,81);
mysql> insert into UserInformation(FirstName,LastName,Age,Marks) values('David','Miller',29,99);
mysql> insert into UserInformation(FirstName,LastName,Age,Marks)
values('Mitchell','Johnson',22,65);使用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 row in set (0.00 sec)