ここから本文です

SQLについて質問です。 ※postgresで実行します。

coc********さん

2017/5/600:50:30

SQLについて質問です。
※postgresで実行します。

以下のようなレコードを持つテーブルがあるとします
ID | フルーツ
-------------------
1 | リンゴ
1 | バナナ
1 | メロン
2 | リンゴ
2 | バナナ
3 | リンゴ
3 | メロン

リンゴを持っているけど、メロンを持っていないidを抽出したいです。
かつ対象のidの所持するフルーツを全てarrayで表示したいです。

SELECT DISTINCT
ta1.ID
, ARRAY_TO_STRING(
ARRAY (
SELECT
フルーツ
FROM
テーブル AS ta2
WHERE
ta1.ID = ID
ORDER BY
フルーツ DESC
)
, ','
)
FROM
テーブル AS ta1
WHERE ta1.フルーツ IN ('リンゴ')
AND ta1.フルーツ NOT IN ('バナナ')

上記のようなSQLを作成しましたが、以下のような期待した結果になりませんでした。
ID | フルーツ
-------------------
1 | リンゴ,バナナ,メロン
2 | リンゴ,バナナ

また、not in句のみで実行しましたが、バナナを持たないレコードが抽出されませんでした。NOT INとはINの反対(=所持していない)ではないのでしょうか?
私の期待する結果をSQLで取得したい場合、どのような書き方が正しいのでしょうか?

閲覧数:
82
回答数:
3
お礼:
500枚

違反報告

ベストアンサーに選ばれた回答

oo_********さん

2017/5/916:43:38

まずは、該当するIDをどうやって抽出するかですね

SELECT id
FROM fruit
WHERE fruit_name IN ('リンゴ')
EXCEPT
SELECT id
FROM fruit
WHERE fruit_name IN ('メロン')

このようにEXCEPT句を使う事で前半のSQLの結果から、後半のSQLに該当する結果を取り除くことが出来ます。

それが出来れば、あとはもとのSQLにその結果を結びつけるだけですね。

SELECT DISTINCT
ta1.id
, ARRAY_TO_STRING( ARRAY( SELECT fruit_name FROM fruit AS ta2 WHERE ta2.id = ta1.id ORDER BY fruit_name ), ',' )
FROM fruit AS ta1
INNER JOIN ( SELECT id
FROM fruit
WHERE fruit_name IN ('リンゴ')
EXCEPT
SELECT id
FROM fruit
WHERE fruit_name IN ('メロン')
) AS ta3 ON ( ta3.id = ta1.id )

複数の条件が出てくることを想定して、あえて IN句で該当キーワードを指定するSQLとしています。

  • oo_********さん

    2017/5/917:34:30

    すいません、テーブルを自分なりに作成したため、テーブル名、項目名がお題と違っていました。

    SELECT DISTINCT
    ta1.id
    , ARRAY_TO_STRING( ARRAY( SELECT フルーツ FROM テーブル AS ta2 WHERE ta2.id = ta1.id ORDER BY フルーツ ), ',' )
    FROM fruit AS ta1
    INNER JOIN ( SELECT id
    FROM テーブル
    WHERE フルーツ IN ('リンゴ')
    EXCEPT
    SELECT id
    FROM テーブル
    WHERE フルーツ IN ('メロン')
    ) AS ta3 ON ( ta3.id = ta1.id )

    こうですね

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

この回答は投票によってベストアンサーに選ばれました!

ベストアンサー以外の回答

1〜2件/2件中

並び替え:回答日時の
新しい順
|古い順

プロフィール画像

カテゴリマスター

nora1962jpさん

2017/5/613:57:20

PostgreSQL 9.0以降なら「STRING_AGG」関数が使えます。

メリットとしては配列→配列の集約という段階を一つで集約可能なこと、集約する項目の並び順を指定できることです。

SELECT ID,STRING_AGG(フルーツ,',' ORDER BY フルーツ)
FROM TBL
WHERE ID IN (SELECT ID FROM TBL WHERE フルーツ='リンゴ')
AND ID NOT IN (SELECT ID FROM TBL WHERE フルーツ='メロン')
GROUP BY ID;

のようになります。(ID順にまとめたい時はORDER BY ID)
あと、IDについて特定のフルーツのありなしについては「IN、NOT IN」だけでなく、「EXISTS、NOT EXITS」も使えます。

SELECT ID,STRING_AGG(フルーツ,',' ORDER BY フルーツ)
FROM TBL T1
WHERE EXISTS ( SELECT 1 FROM TBL T2 WHERE T2.ID=T1.ID AND T2.フルーツ='リンゴ')
AND NOTT EXISTS ( SELECT 1 FROM TBL T3 WHERE T2.ID=T1.ID AND T3.フルーツ='メロン')
GROUP BY ID;

パフォーマンス的にはインデックスの有無などにもよるのでどちらが優れているかは一概に言えません。PostgreSQLはIN条件でセミハッシュ結合、アンチセミハッシュ結合が使えるので。

ARRAY_AGGは8.4以降で使えますがそれを含めてNOT INを使う場合に気をつけなればならないのはNULLについてです。
今回はないかもしれませんが
NULL | メロン
というデータがあるとSQLが返すのは0行です。
こうしたことはNOT EXISTSでは発生しません。

その点は留意してください。

プロフィール画像

カテゴリマスター

原沢信道さん

2017/5/610:01:47

select ID,array_to_string(array_agg(rtrim(フルーツ)),',')
from テーブル where
ID in(select ID from テーブル where フルーツ='リンゴ') and
ID not in(select ID from テーブル where フルーツ='メロン')
GROUP BY ID
ですが。1はメロンを持っているので、答えは2のみとなります。

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる