1.以支付方式A购买过产品A或者以支付方式B购买过产品A
(select customerid
from order
where paymode = A
and product = A)
union
(select customerid
from order
where paymode = B
and product = A)
2.以支付方式A购买过产品A并且以支付方式B购买过产品A
(select customerid
from order
where paymode = A
and product = A)
intersect
(select customerid
from order
where paymode = B
and product = A)
3.没用支付方式A购买过产品A并且以支付方式B购买过产品A
(select customerid
from order
where paymode = B
and product = A)
minus
(select customerid
from order
where paymode = A
and product = A)
4.以支付方式A购买过产品A并且以支付方式A购买过产品B并且以支付方式B购买过产品A
...
5.(以支付方式A购买过产品A并且以支付方式A购买过产品B ) 或者以支付方式B购买过产品A
...
当然也可以用join来写上述sql语句,但使用集合操作逻辑上更清楚。
也许你应该复习一下集合和谓词逻辑的知识啦。
1.以支付方式A购买过产品A或者以支付方式B购买过产品A
select distinct customerid from order where product = A and paymode in (A,B);
2.以支付方式A购买过产品A并且以支付方式B购买过产品A
select customerid from order where product = A and paymode in (A,B)
group by paymode having count(distinct paymode) = 2;
3.没用支付方式A购买过产品A并且以支付方式B购买过产品A
select customerid from (
select customerid,paymode from order where product = A
group by customerid,paymode having count(distinct paymode) = 1)
where paymode = B;