webdevqa.jp.net

MySQLのサブクエリでGROUP_CONCATを使用する

別のテーブルのIDのリストを含めるMySQLクエリがあります。 Webサイトでは、人々は特定のアイテムを追加でき、人々はそれらのアイテムをお気に入りに追加できます。私は基本的に、そのアイテムを気に入った人のIDのリストを取得したいと思います(これは少し簡略化されていますが、これは要するにそれです)。

基本的に、私はこのようなことをします:

SELECT *,
GROUP_CONCAT((SELECT userid FROM favourites WHERE itemid = items.id) SEPARATOR ',') AS idlist
FROM items
WHERE id = $someid

このように、後でコードのPHPでidlistを配列に分割することで、アイテムをお気に入りに追加したユーザーを表示できますが、次のMySQLエラーが発生しています。

1242-サブクエリは複数の行を返します

これは、たとえばCONCAT?の代わりにGROUP_CONCATを使用することのポイントのようなものだと思いました。私はこれについて間違った方法で行っていますか?


OK、これまでの回答に感謝します、それはうまくいくようです。ただし、キャッチがあります。アイテムは、そのユーザーによって追加された場合もお気に入りであると見なされます。したがって、creator = useridであるかどうかを確認するには、追加のチェックが必要になります。誰かが私にこれを行うためのスマートな(そしてうまくいけば効率的な)方法を思い付くのを助けることができますか?

ありがとうございました!

編集:私はこれをやろうとしました:

SELECT [...] LEFT JOIN favourites ON (userid = itemid OR creator = userid)

idlistは空です。 INNER JOINの代わりにLEFT JOINを使用すると、空の結果が得られることに注意してください。 ON要件を満たす行があると確信していますが。

38
Aistina

このようなクエリでは外部スコープの変数にアクセスできません(items.idは使用できません)。むしろ何かを試してみてください

SELECT
    items.name,
    items.color,
    CONCAT(favourites.userid) as idlist
FROM
    items
INNER JOIN favourites ON items.id = favourites.itemid
WHERE
    items.id = $someid
GROUP BY
    items.name,
    items.color;

必要に応じてフィールドのリストを展開します(名前、色...)。

13
soulmerge

OPはほぼ正しく設定しました。 GROUP_CONCATは、 完全なサブクエリ ではなく、サブクエリの列をラップする必要があります(コンマがデフォルトであるため、セパレータを無視しています)。

SELECT i.*,
(SELECT GROUP_CONCAT(userid) FROM favourites f WHERE f.itemid = i.id) AS idlist
FROM items i
WHERE i.id = $someid

これにより、目的の結果が得られます。また、サブクエリで外部スコープ変数にアクセスできるため、受け入れられた回答が部分的に間違っていることも意味します。

88
nietonfir

「userid = itemid」が間違っている可能性があると思います。次のようにすべきではありません。

SELECT ITEMS.id,GROUP_CONCAT(FAVOURITES.UserId) AS IdList
FROM FAVOURITES 
INNER JOIN ITEMS ON (ITEMS.Id = FAVOURITES.ItemId OR FAVOURITES.UserId = ITEMS.Creator)
WHERE ITEMS.Id = $someid
GROUP BY ITEMS.ID
2
Turnkey

はい、soulmergeのソリューションは大丈夫です。しかし、より多くの子テーブルからデータを収集しなければならないクエリが必要でした。たとえば:

  • メインテーブル:sessions(presentation sessions)(uid、name、..)
  • 最初の子テーブル:eventsキーsession_id(uid、session_uid、date、time_start、time_end)
  • 2番目の子テーブル:accessories_needed(ラップトップ、プロジェクター、マイクなど)、キーsession_id(uid、session_uid、accessory_name)
  • 3番目の子テーブル:session_presenters(発表者)キーがsession_id(uid、session_uid、presenter_name、address ...)

すべてのセッションには、子テーブルのテーブルにさらに行があります(より多くのタイムスケジュール、より多くのアクセサリ)

そして、私はすべてのセッションで1つのコレクションに収集して鉱石列に表示する必要がありました(そのうちのいくつか):

session_id | session_name | date | time_start | time_end | accessories | presenters

私の解決策(何時間もの実験の後):

SELECT sessions.uid, sessions.name,
    ,(SELECT GROUP_CONCAT( `events`.date SEPARATOR '</li><li>') 
            FROM `events` 
            WHERE `events`.session_id = sessions.uid ORDER BY `events`.date) AS date
    ,(SELECT GROUP_CONCAT( `events`.time_start SEPARATOR '</li><li>') 
            FROM `events` 
            WHERE `events`.session_id = sessions.uid ORDER BY `events`.date) AS time_start
    ,(SELECT GROUP_CONCAT( `events`.time_end SEPARATOR '</li><li>') 
            FROM `events` 
            WHERE `events`.session_id = sessions.uid ORDER BY `events`.date) AS time_end
    ,(SELECT GROUP_CONCAT( accessories.name SEPARATOR '</li><li>') 
            FROM accessories 
            WHERE accessories.session_id = sessions.uid ORDER BY accessories.name) AS accessories
    ,(SELECT GROUP_CONCAT( presenters.name SEPARATOR '</li><li>') 
            FROM presenters
            WHERE presenters.session_id = sessions.uid ORDER BY presenters.name) AS presenters

    FROM sessions

したがって、JOINやGROUP BYは必要ありません。データをわかりやすく表示する別の便利な機能(「エコー」する場合):

  • events.date、time_start、time_endなどを「<UL> <LI> ... </ LI> </ UL>」でラップして、「<LI> </ LI>」をクエリでセパレータとして使用できますリスト項目の結果を分離します。

これが誰かの助けになることを願っています。乾杯!

1
Ambrus Laszlo

GROUP_CONCATの目的は正しいですが、サブクエリは不要であり、問​​題を引き起こしています。代わりにこれを試してください:

SELECT ITEMS.id,GROUP_CONCAT(FAVOURITES.UserId)
FROM FAVOURITES INNER JOIN ITEMS ON ITEMS.Id = FAVOURITES.ItemId
WHERE ITEMS.Id = $someid
GROUP BY ITEMS.ID
1
Turnkey