MYSQL:您能提取出与 4 个表达式中的 3 个匹配的结果吗?
mysqlmysqli database
您可以使用 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)
使用 insert 命令在表中插入一些记录。 查询语句如下 −
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 row in set (0.00 sec)
