drop TABLE IF EXISTS Customers;
drop TABLE IF EXISTS Orders;
CREATE TABLE IF NOT EXISTS Customers (customer_id INT, customer_name VARCHAR(30));
CREATE TABLE IF NOT EXISTS Orders (order_id INT, customer_id INT, product_name VARCHAR(30));
TRUNCATE TABLE Customers;
INSERT INTO Customers (customer_id, customer_name) VALUES ('1', 'Daniel');
INSERT INTO Customers (customer_id, customer_name) VALUES ('2', 'Diana');
INSERT INTO Customers (customer_id, customer_name) VALUES ('3', 'Elizabeth');
INSERT INTO Customers (customer_id, customer_name) VALUES ('4', 'Jhon');
TRUNCATE TABLE Orders;
INSERT INTO Orders (order_id, customer_id, product_name) VALUES ('10', '1', 'A');
INSERT INTO Orders (order_id, customer_id, product_name) VALUES ('20', '1', 'B');
INSERT INTO Orders (order_id, customer_id, product_name) VALUES ('30', '1', 'D');
INSERT INTO Orders (order_id, customer_id, product_name) VALUES ('40', '1', 'C');
INSERT INTO Orders (order_id, customer_id, product_name) VALUES ('50', '2', 'A');
INSERT INTO Orders (order_id, customer_id, product_name) VALUES ('60', '3', 'A');
INSERT INTO Orders (order_id, customer_id, product_name) VALUES ('70', '3', 'B');
INSERT INTO Orders (order_id, customer_id, product_name) VALUES ('80', '3', 'D');
INSERT INTO Orders (order_id, customer_id, product_name) VALUES ('90', '4', 'C');
SELECT customer_id,customer_name
FROM Customers
WHERE
customer_id IN(SELECT customer_id FROM Orders WHERE product_name='A')
AND
customer_id IN(SELECT customer_id FROM Orders WHERE product_name='B')
AND
customer_id NOT IN(SELECT customer_id FROM Orders WHERE product_name='C')
select DISTINCT o.customer_id, c.customer_name
from Customers c , Orders o
where c.customer_id = o.customer_id
and o.customer_id in (select customer_id from Orders where product_name = 'A')
and o.customer_id in (select customer_id from Orders where product_name = 'B')
and o.customer_id in (
select DISTINCT customer_id
from Orders
where customer_id not in
(select DISTINCT customer_id from Orders where product_name = 'C')
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/205120.html
標籤：其他