mysql - MySQL 查询语句要怎么写？

Question

产品表 t_product 和 产品扩展分类表 t_product_category

t_product 表字段 product_id, category_id, title, seq

t_product_category 表字段 product_id, category_id

一个产品可能有多个扩展分类，现在想要根据category_id来查找产品，如果 t_product 没有，t_product_category有可需要找出来，应该怎么写？

我这样写不可，不知道为什么
SELECT p.* FROM t_product AS p , t_product_category AS pc WHERE ( p.category_id IN ( 1,2 ) OR ( p.product_id = pc.product_id AND pc.category_id IN ( 1,2 ) ) ) GROUP BY p.product_id ORDER BY p.seq ASC, p.product_id DESC LIMIT 0, 20

Answer 1

SELECT p.*
FROM t_product p
LEFT JOIN t_product_category pc ON p.product_id = pc.product_id
WHERE p.category_id = <category_id>
   OR pc.category_id = <category_id>

---

首先将 t_product 表与 t_product_category 表关联起来，然后使用 WHERE 语句过滤出满足条件的记录。如果想只返回唯一的一条记录，可以使用 DISTINCT 关键字来去重。

Answer 2

SELECT p.product_id, p.category_id, p.title, p.seq
FROM t_product p
LEFT JOIN t_product_category c ON p.product_id = c.product_id
WHERE p.category_id = {category_id} OR c.category_id = {category_id};

Answer 3

SELECT p.* 
FROM t_product AS p 
LEFT JOIN t_product_category AS pc ON p.product_id = pc.product_id 
WHERE p.category_id IN (1,2) OR pc.category_id IN (1,2) 
GROUP BY p.product_id 
ORDER BY p.seq ASC, p.product_id DESC 
LIMIT 0, 20