Mysql多条件选择

Question

我需要帮助创建查询表A

+------------+--------------------+-------+
| product_id | name               | price |
+------------+--------------------+-------+
|         13 | Product 13         |     5 |
|         14 | Product 14         |     2 |
|         15 | Product 15         |     3 |
|         16 | Product 16         |     2 |
|         17 | Product 17         |    15 |
+------------+--------------------+-------+

表B

+----+------------+-------------+
| id | product_id | taxonomy_id |
+----+------------+-------------+
| 10 |         13 |           5 |
| 11 |         13 |           2 |
| 12 |         14 |           3 |
| 13 |         15 |           2 |
| 14 |         16 |          15 |
| 14 |         16 |           5 |
| 14 |         16 |          19 |
| 14 |         16 |          21 |
| 14 |         16 |          18 |
+----+------------+-------------+

我的尝试

SELECT *
FROM A
LEFT JOIN B ON B.product_id = A.product_id 
WHERE IF(B.taxonomy_id IN ('5','15'), 
         IF(B.taxonomy_id IN ('2'), 1, 0), 0) = 1
GROUP BY A.product_id

我需要它返回表 A 中那些正确的结果

B.taxonomy_id 为“5”或“15”，B.taxonomy_id 为“2”

此示例的结果是 -> product_id - 13我还需要获取一些结果 SELECT count(*) ... -> return is 1

Answer 1

您的表没有 id 列作为唯一主键，这正常吗？

无论如何，这是我遇到的情况，请告诉我它是否有效:

SELECT table_nameA.product_id
FROM table_nameA
LEFT JOIN table_nameB on table_nameA.product_id = table_nameB.product_id
WHERE taxonomy_id = 2 AND table_nameA.product_id IN
    (SELECT table_nameA.product_id
    FROM table_nameA
    LEFT JOIN table_nameB on table_nameA.product_id = table_nameB.product_id
    where taxonomy_id = 5 or taxonomy_id = 15
    GROUP BY table_nameA.product_id, taxonomy_id)

结果是:

| product_id |
|------------|
|         13 |

关于你的计数查询，是完全一样的。

SELECT count(table_nameA.product_id) as Quantity
FROM table_nameA
LEFT JOIN table_nameB on table_nameA.product_id = table_nameB.product_id
WHERE taxonomy_id = 2 AND table_nameA.product_id IN
    (SELECT table_nameA.product_id
    FROM table_nameA
    LEFT JOIN table_nameB on table_nameA.product_id = table_nameB.product_id
    where taxonomy_id = 5 or taxonomy_id = 15
    GROUP BY table_nameA.product_id, taxonomy_id)

结果是:

| Quantity |
|----------|
|        1 |