命令复杂查询的SQL

Question

假设我有这些桌子：

items，它存储项目。

CREATE TABLE items
(
  id serial NOT NULL,
  name character varying(255),
  rarity character varying(255),
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  CONSTRAINT items_pkey PRIMARY KEY (id)
)

item_modifiers，它管理项和修饰符之间的多到多关系。

CREATE TABLE item_modifiers
(
  id serial NOT NULL,
  item_id integer,
  modifier_id integer,
  primary_value integer,
  secondary_value integer,
  CONSTRAINT item_modifiers_pkey PRIMARY KEY (id)
)

modifiers，它包含所有可能的项修饰符。

CREATE TABLE modifiers
(
  id serial NOT NULL,
  name character varying(255),
  CONSTRAINT explicit_mods_pkey PRIMARY KEY (id)
)

现在假设我有一个复杂的查询。我希望找到所有具有ID 1和2的修饰符的项，这些修饰符是由ID为1的修饰符的primary_value排序的。

我尝试过这个查询

SELECT "items".*, item_modifiers.primary_value FROM "items" 
INNER JOIN item_modifiers ON item_modifiers.item_id = items.id
AND ((item_modifiers.modifier_id = 1) 
OR (item_modifiers.modifier_id = 2)) 
GROUP BY items.id, item_modifiers.primary_value
HAVING 
COUNT(item_modifiers.id) = 2 
ORDER BY item_modifiers.primary_value DESC

但是它返回一个空的结果集。然而，当我不按primary_value分组时，它是这样做的，但是我不能订购它。我被困在这上面已经很久了，所以任何帮助都是非常感谢的。

编辑我已经构建了一个SQL小提琴来演示http://sqlfiddle.com/#!15/30887/1

Answer 1

您不需要使用item_modifiers 2加入modifier_id，您只需要确保存在这样的记录：

SELECT items.*, item_modifiers.primary_value 
FROM items 
INNER JOIN item_modifiers ON item_modifiers.item_id = items.id AND item_modifiers.modifier_id = 1
WHERE EXISTS
(
  SELECT *
  FROM item_modifiers 
  WHERE item_modifiers.item_id = items.id AND item_modifiers.modifier_id = 2
)
ORDER BY item_modifiers.primary_value DESC;

这是您的SQL花招：http://sqlfiddle.com/#!15/30887/9

Answer 2

这就是你要找的吗？

SELECT "items".*, 
       item_modifiers.primary_value 
FROM   "items" 
       INNER JOIN item_modifiers 
               ON item_modifiers.item_id = items.id 
                  AND ( ( item_modifiers.modifier_id = 1 ) 
                         OR ( item_modifiers.modifier_id = 2 ) ) 
ORDER  BY item_modifiers.primary_value DESC;