当列在多行中等于或不等于值时进行匹配

Question

我有一个与其他两个表有多对多关系的表:

CREATE TABLE assoc (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ref1 INT NOT NULL,
  ref2 INT NOT NULL,
  INDEX composite_key (ref1, ref2)
);

我想确定是否有与ref1匹配或不匹配ref2给定值的关联。举个例子，假设我想匹配ref1的关联值为1000，而ref2的关联值为:

INSERT INTO assoc (ref1, ref2) VALUES
  (100, 10),
  (100, 1000);

但是，如果只有值1000与ref1相关联，或者它仅仅是任何其他值，则不应该给出匹配:

INSERT INTO assoc (ref1,ref2) VALUES
  (101, 10),
  (102, 1000);

我想出了两个解决方案。1)创建一个临时表，其中包含与值匹配的行，然后从中选择不匹配的行，2)连接同一个表，并指定该表中不匹配的条件。

CREATE TEMPORARY TABLE set
  SELECT ref1 FROM assoc
  WHERE ref2 = 1000;
SELECT assoc.ref1 FROM `set`
  JOIN assoc ON `set`.ref1 = assoc.ref1
  WHERE assoc.ref2 <> 1000;
SELECT assoc.ref1 FROM assoc 
  JOIN assoc AS `set` ON assoc.ref1 = `set`.ref1
  WHERE assoc.ref2 = 1000 AND `set`.ref2 <> 1000;

但是，我想知道是否有其他方法来完成这个匹配?

Answer 1

我认为你的第二个解决方案是做你想做的事的标准方法;我也会这么做。并且正确添加了INDEX composite_key 。但是，您可以添加一个额外的GROUP BY来避免相同的关联。Ref1出现的次数与连接找到与ref2 <> 1000:

相关的行次数相同。

SELECT assoc.ref1 FROM assoc 
  JOIN assoc AS `set` ON assoc.ref1 = `set`.ref1
  WHERE assoc.ref2 = 1000 AND `set`.ref2 <> 1000
  GROUP BY assoc.ref1;