设置 fiddle
CREATE TABLE ORDERS (cust INT, brand VARCHAR(100))
INSERT INTO ORDERS VALUES (1, 'Sony')
INSERT INTO ORDERS VALUES (1, 'Sony')
INSERT INTO ORDERS VALUES (1, 'Sony')
INSERT INTO ORDERS VALUES (1, 'Hp')
INSERT INTO ORDERS VALUES (1, 'Hp')
INSERT INTO ORDERS VALUES (2, 'Sony')
INSERT INTO ORDERS VALUES (2, 'Sony')
INSERT INTO ORDERS VALUES (2, 'Hp')
INSERT INTO ORDERS VALUES (2, 'Hp')
INSERT INTO ORDERS VALUES (2, 'Hp')
INSERT INTO ORDERS VALUES (2, 'Hp')
INSERT INTO ORDERS VALUES (3, 'Sony')
INSERT INTO ORDERS VALUES (3, 'Sony')
INSERT INTO ORDERS VALUES (3, 'Sony')
INSERT INTO ORDERS VALUES (3, 'Hp')
INSERT INTO ORDERS VALUES (3, 'Hp')
查询
方法1
select
sony.custId
from
(select cust as custId, Brand, count(*) as count
from orders
where Brand = 'Sony'
group by cust, brand) Sony
inner join
(select cust as custId, Brand, count(*) as count
from orders
where Brand = 'Hp'
group by cust, brand) Hp
on sony.custId = hp.CustId
Where
sony.count > 2 and
hp.count < 5
这对你理解关系很有帮助。这是另一种方法
第二种方法
select cust
from orders
group by cust, brand
having count(case brand when 'Sony' then 'x' end) > 2 and
count(case brand when 'Hp' then 'x' end) < 5
时间大致相同 - 当然会根据数据量、索引等而有所不同。使用Sql fiddle检查执行计划。第一种方法似乎有更多的事情要做。但这并不意味着在生产中使用时会更糟。