gpt4 book ai didi

Mysql子查询结果在where子句中

转载 作者:行者123 更新时间:2023-11-29 08:06:10 24 4
gpt4 key购买 nike

我正在尝试获取子查询 sale 的结果并在 WHERE 子句中使用它......我想排除等于 1 的销售额

有办法做到这一点吗?

SELECT 
p.product_id,
etp_vendor.active,
(SELECT AVG(rating) AS total FROM etp_review r1 WHERE r1.product_id = p.product_id AND r1.`status` = '1' GROUP BY r1.product_id) AS rating,
(SELECT etp_category.sale FROM etp_category WHERE etp_category.category_id = etp_product_to_category.category_id ORDER BY etp_category.sale LIMIT 1) AS sales,
(SELECT pd2.price FROM etp_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND (pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW()) ORDER BY pd2.priority, pd2.price LIMIT 1) AS discount,
(SELECT ps.price FROM etp_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND (ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW()) ORDER BY ps.priority, ps.price LIMIT 1) AS special
FROM
etp_product p
LEFT OUTER JOIN etp_product_description pd ON (p.product_id = pd.product_id)
LEFT OUTER JOIN etp_product_to_store p2s ON (p.product_id = p2s.product_id)
INNER JOIN etp_product_to_vendor ON (p.product_id = etp_product_to_vendor.product_id)
INNER JOIN etp_product_to_category ON (p.product_id = etp_product_to_category.product_id)
INNER JOIN etp_vendor ON (etp_product_to_vendor.vendor_id = etp_vendor.vendor_id)
WHERE
pd.language_id = '1' AND
p.`status` = '1' AND
etp_vendor.active = '1' AND
p.date_available <= NOW() AND
p2s.store_id = '0'
GROUP BY
p.product_id
ORDER BY
p.sort_order,
LCASE(pd.name)

最佳答案

是的,您可以使用HAVING子句

GROUP BY
p.product_id
HAVING sales != 1 /* or HAVING sales <> 1*/
ORDER BY
p.sort_order,
LCASE(pd.name)

关于Mysql子查询结果在where子句中,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/22693330/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com