mysql - 选择 3 行包含单独值的列 - 自连接？

Question

寻找执行 select 语句的最佳方法。我认为自连接是最好的方法，但我在将其组合在一起时遇到了困难。任何帮助或指导将不胜感激。

我有一个项目元表:

+----------+----------+-------+
| item_num | field_id | meta  |
+----------+----------+-------+
|      800 |      508 | red   |
|      800 |      507 | shirt |
|      800 |      504 | 2     |
|      700 |      508 | red   |
|      700 |      507 | shirt |
|      700 |      504 | 3     |
|      500 |      508 | red   |
|      500 |      507 | shirt |
|      500 |      504 | 5     |
+----------+----------+-------+

从表 WHERE 中选择 item_num:

1. field_id=508 和 meta=red
2. field_id=507 和元=衬衫
3. field_id=504 和元=2

我最初的陈述想法:

SELECT   a.item_num
FROM     Table a
  JOIN   Table b
  JOIN   Table c
      ON a.item_num = b.item_num = c.item_num
     AND a.field_id = 508
     AND a.meta = red
     AND b.field_id = 507
     AND b.meta = shirt
     AND c.field_id = 504
     AND c.meta = 2
GROUP BY a.item_num
LIMIT 1

Answer 1

使用分组更容易

 SELECT item_num
 FROM YourTable
 GROUP BY item_num
 HAVING COUNT(CASE WHEN field_id=508 AND meta='red' THEN 1 END) > 0
    AND COUNT(CASE WHEN field_id=507 AND meta='shirt' THEN 1 END) > 0
    AND COUNT(CASE WHEN field_id=504 AND meta='2' THEN 1 END) > 0

也可以写成

 HAVING COUNT(CASE WHEN field_id=508 AND meta='red'   THEN 1 
                   WHEN field_id=507 AND meta='shirt' THEN 1 
                   WHEN field_id=504 AND meta='2'     THEN 1 
              END) = 3