MySQLで4つの条件のうち3つ以上に一致するレコードを取得する方法

MySQLでは、複数の条件のうち一部だけが一致するレコードを抽出したいケースがあります。たとえば「4つの条件のうち、少なくとも3つに一致する行だけを取得したい」といった場合です。そんなときはCASE文を組み合わせることで実現できます。

基本となる構文

各条件が一致した場合に「1」、一致しなかった場合に「0」を返すCASE式を用意し、その合計値が3以上になるかどうかをWHERE句で判定します。構文は以下のとおりです。

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;

このように、各CASE式の結果を足し合わせることで「何個の条件に一致したか」を数値として評価できるのがポイントです。しきい値を変更すれば、「2つ以上一致」「すべて一致」といった柔軟な検索も可能になります。

サンプルテーブルを作成する

それでは、実際に動きを確認してみましょう。まずはユーザー情報を格納するテーブルを作成します。

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)

4つの条件のうち3つ以上に一致するレコードを抽出する

ここからが本題です。次の4つの条件を用意し、そのうち3つ以上に一致するレコードのみを取得します。

• FirstName が「Mitchell」であること
• LastName が「Johnson」であること
• Age が「22」であること
• Marks が「67」であること

クエリは以下のように記述します。

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)

Id 5の「Mitchell Johnson」のレコードが取得できました。このレコードは FirstName・LastName・Age の3条件に一致しています（Marks は67ではなく65なので不一致）。つまり「4つのうち3つ一致」という条件を満たしているため、正しく抽出されたことがわかります。

まとめ

CASE式で各条件の一致を1/0に変換して合計すれば、ANDやORだけでは表現しづらい「部分一致による絞り込み」をシンプルに実装できます。あいまい検索やスコアリング的なデータ抽出に応用できる便利なテクニックなので、ぜひ覚えておきましょう。