sql - 连接集必须包含所有值但可能包含更多值的SQL

我有三个表offers，sports和连接表offers_sports。

class Offer < ActiveRecord::Base
  has_and_belongs_to_many :sports
end

class Sport < ActiveRecord::Base
  has_and_belongs_to_many :offers
end

我想选择的报价，包括一个给定的运动名称数组。它们必须包含所有sports但可能包含更多。
假设我有三个提议：

light:
  - "Yoga"
  - "Bodyboarding"
medium:
  - "Yoga"
  - "Bodyboarding"
  - "Surfing"
all:
  - "Yoga"
  - "Bodyboarding"
  - "Surfing"
  - "Parasailing"
  - "Skydiving"

给定数组["Bodyboarding", "Surfing"]我想得到medium和all但不想得到light。
我尝试了一些this answer的方法，但结果中没有行：

Offer.joins(:sports)
     .where(sports: { name: ["Bodyboarding", "Surfing"] })
     .group("sports.name")
     .having("COUNT(distinct sports.name) = 2")

转换为SQL:

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;

一个ActiveRecord的答案会很好，但我只接受SQL，最好是Postgres兼容。
数据：

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

最佳答案

按offer.id分组，而不是按sports.name（或sports.id）分组：

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;

假设典型实现：
offer.id和sports.id被定义为主键。
sports.name定义为唯一。
(sport_id, offer_id)中的offers_sports定义为唯一（或PK）。
您不需要在计数中DISTINCT。而且count(*)更便宜一点。
相关的答案与可能的技术：
How to filter SQL results in a has-many-through relation
由@max（OP）添加-这是以上查询滚动到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(*) = ?", sport_names.size)
  end
end

关于sql - 连接集必须包含所有值但可能包含更多值的SQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36131803/