mysql - 使用 mySQL 从其他行中选择具有多个值的行

Question

假设我有下表，名为data:

ID   foo1     foo2    foo3
1    11       22      33
2    22       17      92
3    31       33      53
4    53       22      11
5    43       23      9

我想选择所有行，其中 foo1、foo2 或 foo3 与第一行中的这些列中的任何一个相匹配。也就是说，我希望所有行中至少有一个 foo 也出现在第一行中。在上面的示例中，我想选择第 1、2、3 和 4 行。我认为我可以使用类似的东西

SELECT * FROM data WHERE foo1 IN (SELECT foo1,foo2,foo3 FROM data WHERE ID=1)
                      OR foo2 IN (SELECT foo1,foo2,foo3 FROM data WHERE ID=1)
                      OR foo3 IN (SELECT foo1,foo2,foo3 FROM data WHERE ID=1)

但这似乎行不通。我当然可以使用

WHERE foo1=(SELECT foo1 FROM data WHERE ID=1) 
   OR foo1=(SELECT foo2 FROM data WHERE ID=1) 
   OR ...

但这会涉及很多行，而在我的真实数据集中实际上有 16 列，因此这确实会导致腰部疼痛。有更复杂的方法吗？

此外，如果我还想计算命中数(在上面的示例中，第 1 行获得 4 个，第 4 行获得 2 个，第 2,3 行获得 1 个)，我应该怎么做？

Answer 1

SELECT data.*,
      (data.foo1 IN (t.foo1, t.foo2, t.foo3))
    + (data.foo2 IN (t.foo1, t.foo2, t.foo3))
    + (data.foo3 IN (t.foo1, t.foo2, t.foo3)) AS number_of_hits
FROM   data JOIN data t ON t.id = 1
WHERE  data.foo1 IN (t.foo1, t.foo2, t.foo3)
    OR data.foo2 IN (t.foo1, t.foo2, t.foo3)
    OR data.foo3 IN (t.foo1, t.foo2, t.foo3)

查看 sqlfiddle .

实际上，经过深思熟虑，您可能会考虑规范化您的数据:

CREATE TABLE data_new (
  ID         BIGINT  UNSIGNED NOT NULL,
  foo_number TINYINT UNSIGNED NOT NULL,
  val        INT,
  PRIMARY KEY (ID, foo_number),
  INDEX (val)
);

INSERT INTO data_new
  (ID, foo_number, val)
          SELECT ID, 1, foo1 FROM data
UNION ALL SELECT ID, 2, foo2 FROM data
UNION ALL SELECT ID, 3, foo3 FROM data;

DROP TABLE data;

然后你可以这样做:

SELECT   ID,
         MAX(IF(foo_number=1,val,NULL)) AS foo1,
         MAX(IF(foo_number=2,val,NULL)) AS foo2,
         MAX(IF(foo_number=3,val,NULL)) AS foo3,
         number_of_hits
FROM     data_new JOIN (
  SELECT   d1.ID, COUNT(*) AS number_of_hits
  FROM     data_new d1 JOIN data_new d2 USING (val)
  WHERE    d2.ID = 1
  GROUP BY d1.ID
) t USING (ID)
GROUP BY ID

查看 sqlfiddle .

正如您从执行计划中看到的那样，这对于大型数据集来说效率会高得多。