Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal StudiesMYSQL：你能提取與 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) -> ); 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)
更新於：2019 年 7 月 30 日
54 次瀏覽
廣告