INの値が重複した時、複数行取得したい

Question

accounts テーブルは表1のようになっています。

表1. accountsテーブル

id	name
1	大塚
2	山田
3	斎藤
4	杉村
5	武田

---

select * from accounts where id in(1, 2)
とすると表2の情報が取得できます。

表2

id	name
1	大塚
2	山田

---

select * from accounts where id in(1, 1, 2)
とすると表3のような情報を取得したいのですが、
実行すると表2の結果となります。
重複分は消されてしまいます。

表3

id	name
1	大塚
1	大塚
2	山田

環境

MariaDB 15.1
PHP 5.6.24

質問

どのようにすればINで指定した数だけ、重複ありで取得できますでしょうか？

アプリケーションで実装すると、汚くなるので出来る限りSQLだけで完結させたいです。

$accountIds = array(1, 1, 2);
$results = $db->query('select id, name from accounts where id in('. join(',', $accountIds). ')');

$accountIdAndNameList = array();

foreach($accountIds as $accountId)
{
  foreach($results as $x)
  {
      if($accountId == $x->id)
      {
          $data = new stdClass();
          $data->id = $x->id;
          $data->name = $x->name;

          array_push($accountIdAndNameList, $data);
      }
  }
}

Comments

[KiyoshiMotoki]

お使いのDB製品の名称（例えば MySQL や PostgerSQL など）を、タグまたは質問欄に追記してください。それによって、解決方法が変わる可能性がありますので。

Answer 1

とうの昔にクローズしているQAですが、どうしてもDB側の世界で解決することを強いられた場合のご参考に回答しておきます。

とはいっても今回のようなケースは、
どちらかというとレアケースと考えていますが。

#MySQL系以外の主要なDBMSの場合
例えばOracle、SQL Server、PostgreSQLではユーザ定義関数(以下UDFと書きます)の戻り値でテーブル型を返却できます。
厳密にいうとOracleはユーザ独自定義したTYPE型の返却なので、返却するテーブル型と同構造のTYPE型を作成して戻り値に指定する必要があります。

少し横にそれましたが、
それを用いるとFROM句に関数を記載でき、さもテーブルのように扱えるのです。

それがどうしたの？、
となるかもしれませんが、
これができるということはJOINも当然できますよねというお話になる訳です。

IN句に与えるカンマ区切りパラメータを引数に与えて、
その条件をテーブル型として返却するUDFを作れば以下のようなことができちゃう訳です。

SELECT
    a.*
FROM
    accounts a
    -- INNER JOIN TABLE(make_cond_tbl('1, 1, 2')) c ← Oracle風
    INNER JOIN make_cond_tbl('1, 1, 2') c
      ON a.id = c.cond

作成したUDFが素直に3行検索値を返しさえすれば、
恐らく要望通りの動作をしてくれるなどはないでしょうか。

#MySQLの場合
恐らくMariaDBもそうなんだろうなと踏んでいますが、
残念なことに今の所はUDFでテーブル型を戻す手段はないようです。
(調べた限り、STRING|INTEGER|REAL|DECIMAL系の戻り値しか不可のようです。)

では上記のようなアプローチは不可能か、
というと不可能ではなく回りくどい方法になりますがストアドプロシージャ(PROCEDURE)を用いると似たようなことは可能なようです。

英語ですが、こちらが参考になるでしょう。

今回のQAでいくと以下のようなイメージでしょうか。

CALL make_cond_tbl('1, 1, 2');
SELECT
    a.*
FROM
    accounts a
    INNER JOIN cond_tbl c
      ON a.id = c.cond

ただし上記の手法をとると、
検索条件を実際に格納するテーブルが必要となるのでご注意ください。

---

恐らく課題はとっくの昔に解決してるとはずなので、
こういったアプローチもあるよ、というご参考程度に。

Comments

[Panzer_vor]

補足ですが、
PostgreSQLは「REGEXP_SPLIT_TO_TABLE」関数で出来るので自作は不要です。

Answer 2

情報の追記、ありがとうございます。

SQLだけで完結

しようとすると、少なくとも私には、以下のようにUNION ALLを使用する方法しか思いつきません。。

SELECT * FROM accounts WHERE id = 1
UNION ALL
SELECT * FROM accounts WHERE id = 1
UNION ALL
SELECT * FROM accounts WHERE id = 2;

mysql> CREATE TABLE accounts (
    ->   id int PRIMARY KEY AUTO_INCREMENT,
    ->   name varchar(16)
    -> ) DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO accounts (name) VALUES
    -> ('大塚'),
    -> ('山田'),
    -> ('斎藤'),
    -> ('杉村'),
    -> ('武田');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM accounts;
+----+--------+
| id | name   |
+----+--------+
|  1 | 大塚   |
|  2 | 山田   |
|  3 | 斎藤   |
|  4 | 杉村   |
|  5 | 武田   |
+----+--------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM accounts WHERE id = 1
    -> UNION ALL
    -> SELECT * FROM accounts WHERE id = 1
    -> UNION ALL
    -> SELECT * FROM accounts WHERE id = 2;
+----+--------+
| id | name   |
+----+--------+
|  1 | 大塚   |
|  1 | 大塚   |
|  2 | 山田   |
+----+--------+
3 rows in set (0.00 sec)

---

もっとも、「SQLだけで完結」することにこだわらなければ、
PHPコードを以下のように実装すれば、同じ結果を取得できます。
（手元に MariaDB の環境が無いため、MySQL5.7 で動作確認）

<?php
$db = new PDO(...);

$accountIds = array(1, 1, 2);
$stmt = $db->prepare('select id, name from accounts where id = ?');

$accountIdAndNameList = array();

foreach($accountIds as $accountId)
{
    $stmt->bindValue(1, $accountId, PDO::PARAM_INT);
    $stmt->execute();

    while (($data = $stmt->fetch(PDO::FETCH_OBJ)) !== false) {
        $accountIdAndNameList[] = $data;
    }
}

var_dump($accountIdAndNameList);

実行結果

array(3) {
  [0]=>
  object(stdClass)#3 (2) {
    ["id"]=>
    string(1) "1"
    ["name"]=>
    string(4) "大塚"
  }
  [1]=>
  object(stdClass)#4 (2) {
    ["id"]=>
    string(1) "1"
    ["name"]=>
    string(4) "大塚"
  }
  [2]=>
  object(stdClass)#5 (2) {
    ["id"]=>
    string(1) "2"
    ["name"]=>
    string(4) "山田"
  }
}

個人的には、それほど「汚い」コードとは思いませんが、いかがでしょうか？

Comments

[aglkjggg]

ご回答ありがとうございます。

foreachがネストせず、ifの比較がないので書いていただいたPHPのほうが可読性は高いですが、
UNION ALLや、書いていただいたPHPのプログラムだと
SELECTがidの数だけ走ってしまうのでコストが高そうですね…

単純にレコードを取得するだけなので、
私の知らないSQLの句をSQLに加えるだけで簡単に複数取得でき、プログラムが短くなるかも…
という思いで質問しましたが、現状が一番ベターな気がしてきました。。。

テーブルの作成やPHPコードで検証等していただきありがとうございました。

簡単にすることはできず、「こういうもの」という理解をすることが出来ました。
ありがとうございます。

[KiyoshiMotoki]

aglkjggg様

レスありがとうございます。

> UNION ALLや、書いていただいたPHPのプログラムだと
> SELECTがidの数だけ走ってしまうのでコストが高そうですね…

検索する id の個数や accountテーブルのレコード数にもよりますが、
idカラムにインデックスが張ってあれば、気にするほどのコストにはならないと思いますよ。

加えて、"PHPのプログラム"の方は、
　$stmt = $db->prepare('select id, name from accounts where id = ?');

の前に以下の一文を実行しておくと、SQL文の解析が1回だけで済むので、さらに性能が改善できるかも知れません。
　$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

http://php.net/manual/ja/pdo.setattribute.php
> PDO::ATTR_EMULATE_PREPARES プリペアドステートメントのエミュレーションを有効または無効にする。

もっとも、MariaDB が PDO からのプリペアドステートメントの使用をサポートしているかはよく分からないので、
断言はできませんが。。