SELECT COUNT(DISTINCT p.product_id) AS total FROM product p
LEFT JOIN product_description pd ON (p.product_id = pd.product_id)
LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id)
LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id)
LEFT JOIN product_option_value pov ON (p.product_id = pov.product_id)
WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW()
AND p2s.store_id = '0' AND p2c.category_id = '237';查询一个语言的一个商店的一个分类 的有某个属性的值的产品总数
LEFT JOIN product_description pd ON (p.product_id = pd.product_id)
LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id)
LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id)
LEFT JOIN product_option_value pov ON (p.product_id = pov.product_id)
WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW()
AND p2s.store_id = '0' AND p2c.category_id = '237';查询一个语言的一个商店的一个分类 的有某个属性的值的产品总数
LEFT JOIN (select * from product_description where language_id = '1') pd ON (p.product_id = pd.product_id)
LEFT JOIN (select * from product_to_store WHERE store_id = '0') p2s ON (p.product_id = p2s.product_id)
LEFT JOIN (select * from product_to_category where category_id = '237') p2c ON (p.product_id = p2c.product_id)
LEFT JOIN product_option_value pov ON (p.product_id = pov.product_id)
试试这样
因为 LEFT JOIN 产生的与右边表不匹配的记录行,
会因为 pd.language_id = '1'(其它两个同理) 这个条件被过滤掉,
其结果实际上是 INNER JOIN 的结果
另外 NOW() 你这个是mysql??
语句执行要多久?SELECT COUNT(DISTINCT p.product_id) AS total FROM product p
inner JOIN product_description pd ON (p.product_id = pd.product_id)
inner JOIN product_to_store p2s ON (p.product_id = p2s.product_id)
inner JOIN product_to_category p2c ON (p.product_id = p2c.product_id)
LEFT JOIN product_option_value pov ON (p.product_id = pov.product_id)
WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW()
AND p2s.store_id = '0' AND p2c.category_id = '237';
CUP 还是超高的,本地都超过了50%以上
还是50%,60%以上,我的是MYSQL