大家好,我有一个SQL:
SELECT p.* FROM products p WHERE required_product_id IS NULL
UNION ALL
SELECT p.* FROM products p, orders o WHERE p.required_product_id = o.product_id
AND o.user_id = 1
UNION DISTINCT
SELECT p.`*` FROM products p, orders o WHERE p.id NOT IN (SELECT product_id FROM orders WHERE product_id = p.id AND o.user_id = 1)
AND p.max_buys = 1;
此查询首先检查物料是否已购买,并显示下一个物料!我想检查用户是否购买了该产品,以便只退回用户未购买的产品
表结构= Products:http://prntscr.com/k6ogp4,Orders:http://prntscr.com/k6ogrz
产品上的max_buys列(如果是1,可以买一次,如果是0,可以买很多次)
发布于 2018-07-16 01:06:16
在您的情况下,我更喜欢使用灵活的查询来管理需求,而且我认为在这种情况下,如果您的描述是完整的,则UNION不是必需的。
SELECT w.* from (
SELECT
(SELECT count(o.product_id) FROM orders o WHERE o.product_id = p.id AND o.user_id = 1) bought_count,
(SELECT count(q.product_id) FROM orders q WHERE q.product_id = p.required_product_id AND q.user_id = 1) order_depend,
p.*
FROM products p ) w
where
(order_depend>0 or required_product_id is null) and -- unlock order depended products
(max_buys=0 or -- can buy more than once
bought_count=0) -- or not bought yet
order by
order_depend desc, -- dependent products to ordered products in first level
bought_count asc, -- not bought products in second level
recommended desc -- recommended products in third level
您也可以根据自己的需求管理任何其他订单。
https://stackoverflow.com/questions/51348957
复制相似问题