mysql一个依赖于其他的

I want to make count of products which has been disabled and have options. First I try to check in one table oc_product are product is disabled and then I want to check by this status (if disabled product) and product id check in another table are this product id have options.

我想计算已被禁用并有选择权的产品。首先我尝试检查一个表oc_product产品被禁用然后我想检查这个状态（如果禁用产品）和产品ID检查另一个表是这个产品ID有选项。

I try like this, but thus only show result of status=0 products.

我尝试这样，但只显示status = 0产品的结果。

select COUNT(*) 
FROM oc_product 
WHERE status=0 
  AND product_id IN ( SELECT product_id FROM oc_product_option)

2 个解决方案

#1

If I unrestood your question, you want to join the tables.

如果我不解决你的问题，你想加入这些表格。

SELECT COUNT(*) FROM oc_product ocp
INNER JOIN oc_product_option ocpo ON ocpo.product_id = ocp.id
WHERE ocp.status=0

so only those rows will returns where product_id in option table ( ocpo.product_id = ocp.id)

所以只有那些行将返回选项表中的product_id（ocpo.product_id = ocp.id）

#2

If I understand correctly, you want a count of all the products with status 0, and also a count of all the products with a status of 0 which have options.

如果我理解正确，您需要计算状态为0的所有产品，并且还要计算状态为0且具有选项的所有产品。

If so, LEFT OUTER JOIN products with options. Then you can count the DISTINCT product_id from the products table to get the count of product, and you can count the DISTINCT product_id from the product options table to get a count of the products with options. COUNT(column_name) doesn't count rows where the column_name value is NULL, hence won't count products where no matching options were found.

如果是这样，LEFT OUTER JOIN产品有选项。然后，您可以从products表中计算DISTINCT product_id以获取产品计数，并且您可以从产品选项表中计算DISTINCT product_id以获得带有选项的产品计数。 COUNT（column_name）不计算column_name值为NULL的行，因此不计算未找到匹配选项的产品。

SELECT COUNT(DISTINCT oc_product.product_id) AS product_count,
        COUNT(DISTINCT oc_product_option.product_id) AS product_with_options_count
FROM oc_product 
LEFT OUTER JOIN oc_product_option
ON oc_product.product_id = oc_product_option.product_id
WHERE oc_product.status = 0

#1