主页 / 问题 / 808280
Accepted
r.mcreal
Asked:2020-04-02 17:15:50 +0800 CST2020-04-02 17:15:50 +0800 CST

按类别 id 获取所有产品

  • 772

你好！有一个对返回所有产品的数据库的查询：

SELECT SQL_CALC_FOUND_ROWS 
       p.product_id as 'product_id', 
       p.image as 'p.image', 
       p.price as 'p.price', 
       pd.name as 'pd.name', 
       ms.seller_id as 'seller_id', 
       ms.nickname as 'ms.nickname', 
       mp.product_status as 'mp.product_status', 
       mp.product_approved as 'mp.product_approved', 
       mp.number_sold as 'mp.number_sold', 
       mp.list_until as 'mp.list_until', 
       p.date_added as 'p.date_created', 
       p.date_modified as 'p.date_modified', 
       pd.description as 'pd.description' 
FROM oc_product p 
INNER JOIN oc_product_description pd USING(product_id)
LEFT JOIN oc_ms_product mp USING(product_id)
LEFT JOIN oc_ms_seller ms USING (seller_id) 
WHERE 1 = 1 AND ms.seller_id = 27 AND product_status IN (1) 
GROUP BY p.product_id 
HAVING 1 = 1 
ORDER BY pd.name ASC LIMIT 0, 12

例如，您需要参考该表oc_product_to_category（其中包含字段product_id和category_id）并仅使用 =1 的那些产品category_id。我不知道该怎么做

mysql

1 个回答

  1. Best Answer
    SELECT SQL_CALC_FOUND_ROWS p.product_id as 'product_id', p.image as 
'p.image', p.price as 'p.price', pd.name as 'pd.name', ms.seller_id as 
'seller_id', ms.nickname as 'ms.nickname', mp.product_status as 
'mp.product_status', mp.product_approved as 'mp.product_approved', 
mp.number_sold as 'mp.number_sold', mp.list_until as 'mp.list_until', 
p.date_added as 'p.date_created', p.date_modified as 'p.date_modified', 
pd.description as 'pd.description' 
FROM oc_product p 
INNER JOIN oc_product_description pd USING(product_id)
LEFT JOIN oc_ms_product mp USING(product_id) 
LEFT JOIN oc_ms_seller ms USING (seller_id) 
INNER JOIN oc_product_to_category  pc USING(product_id)
WHERE ms.seller_id = 27 AND product_status IN (1) AND pc.category_id = 1
GROUP BY p.product_id 
HAVING 1 = 1 ORDER BY pd.name ASC LIMIT 0, 12

    哦，类似的，也许我在某个地方犯了一个错误

    • 2

相关问题