3.查询哪些用户买了‘打印纸’但没有买‘墨盒’(多种方法)
时间: 2024-06-03 19:08:38 浏览: 12
方法1:
SELECT DISTINCT u.username
FROM users u
JOIN orders o1 ON u.user_id = o1.user_id
JOIN order_items oi1 ON o1.order_id = oi1.order_id
JOIN products p1 ON oi1.product_id = p1.product_id
LEFT JOIN orders o2 ON o1.user_id = o2.user_id
LEFT JOIN order_items oi2 ON o2.order_id = oi2.order_id
LEFT JOIN products p2 ON oi2.product_id = p2.product_id
WHERE p1.product_name = '打印纸'
AND p2.product_name IS NULL
AND p1.category_id = (SELECT category_id FROM categories WHERE category_name = '办公用品');
方法2:
SELECT DISTINCT u.username
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi1 ON o.order_id = oi1.order_id
JOIN products p1 ON oi1.product_id = p1.product_id
WHERE p1.product_name = '打印纸'
AND p1.category_id = (SELECT category_id FROM categories WHERE category_name = '办公用品')
AND u.user_id NOT IN
(SELECT u.user_id
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi2 ON o.order_id = oi2.order_id
JOIN products p2 ON oi2.product_id = p2.product_id
WHERE p2.product_name = '墨盒'
AND p2.category_id = (SELECT category_id FROM categories WHERE category_name = '办公用品'));
方法3:
SELECT DISTINCT u.username
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IN
(SELECT o1.order_id
FROM order_items oi1
JOIN products p1 ON oi1.product_id = p1.product_id
JOIN orders o1 ON oi1.order_id = o1.order_id
WHERE p1.product_name = '打印纸'
AND p1.category_id = (SELECT category_id FROM categories WHERE category_name = '办公用品'))
AND u.user_id NOT IN
(SELECT u.user_id
FROM order_items oi2
JOIN products p2 ON oi2.product_id = p2.product_id
JOIN orders o2 ON oi2.order_id = o2.order_id
WHERE p2.product_name = '墨盒'
AND p2.category_id = (SELECT category_id FROM categories WHERE category_name = '办公用品'));