SQL で特定のスポーツのセット (おそらくそれ以上) をすべて含むオファーを選択するにはどうすればよいですか?

SQL: WHERE 結合セットにはすべての値が含まれている必要がありますが、さらに多くの値が含まれる場合があります

SQL では、「WHERE JOINED SET」句により、結合テーブルの結果セットには特定の値が含まれます。ただし、結合されたテーブルには、条件の一部ではない追加の値も含まれる場合があります。この概念を実際に実装するのは、特に条件にどの値を含めるかを決定する場合に難しい場合があります。

次の例を考えてみましょう:

シナリオ:

オファー、スポーツ、およびオファー_スポーツという 3 つのテーブルがあり、オファー、スポーツ、およびそれらの間の関係を表します。指定された一連のスポーツ名を含むオファーを選択するとします。オファーにはすべてのスポーツが含まれている必要がありますが、追加のスポーツが含まれる場合もあります。

データ:

offers
| id | name |
| --- | ---- |
| 1 | light |
| 2 | medium |
| 3 | all |
| 4 | extreme |

sports
| id | name |
| --- | ---- |
| 1 | Yoga |
| 2 | Bodyboarding |
| 3 | Surfing |
| 4 | Parasailing |
| 5 | Skydiving |

offers_sports
| offer_id | sport_id |
| --- | ---- |
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
| 3 | 5 |
| 4 | 3 |
| 4 | 4 |
| 4 | 5 |

望ましい結果:

配列 ["Bodyboarding", "Surfing"] を指定すると、クエリは中程度のオファーとすべてのオファーを返す必要があります。指定されたスポーツの両方が含まれているためです。両方のスポーツが含まれていないため、オファー ライトは返されません。

間違ったクエリ:

次のクエリでは、スポーツ名ごとにグループ化され、正確に 2 つのスポーツが含まれていることを確認します。スポーツは各オファーに含まれており、返品はできません結果:

SELECT "offers".* 
FROM "offers" 
INNER JOIN "offers_sports" ON "offers_sports"."offer_id" = "offers"."id"     
INNER JOIN "sports" ON "sports"."id" = "offers_sports"."sport_id" 
  WHERE "sports"."name" IN ('Bodyboarding', 'Surfing') 
GROUP BY sports.name 
HAVING COUNT(distinct sports.name) = 2;

解決策:

正しいクエリは、スポーツ名ではなくオファー ID でグループ化し、COUNT( を使用して各オファーに含まれる個別のスポーツの数を確認します。 DISTINCT):

SELECT o.*
FROM   sports        s
JOIN   offers_sports os ON os.sport_id = s.id
JOIN   offers        o  ON os.offer_id = o.id
WHERE  s.name IN ('Bodyboarding', 'Surfing') 
GROUP  BY o.id  -- !!
HAVING count(*) = 2;

このクエリは、指定されたオファーの両方を含むため、オファーのメディアとすべてを返します。スポーツ。

ActiveRecord の実装:

class Offer < ActiveRecord::Base
  has_and_belongs_to_many :sports
  def self.includes_sports(*sport_names)
    joins(:sports)
      .where(sports: { name: sport_names })
      .group('offers.id')
      .having("COUNT(DISTINCT sports.name) = ?", sport_names.size)
  end
end

以上がSQL で特定のスポーツのセット (おそらくそれ以上) をすべて含むオファーを選択するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。