作者TeemingVoid (TeemingVoid)
看板Database
标题Re: [SQL ] 相同栏位两条件
时间Fri Mar 14 16:18:20 2014
※ 引述《Falconeye (未识绮罗香)》之铭言：
: 两个栏位如下：
: customer_id product_code
: 1 VF081
: 1 VF082
: 2 VF081
: 2 VF082
: 3 VF081
: 4 VF081
: 4 VF081
: 请教，要如何算出同时有VF081跟VF082的customer数量？
: 即id=1,2是符合条件的，答案为2笔
分别找出有订VF081与VF082的客户，然後取两者的交集。
举例来说:
create table test0314 (customer_id int, product_code varchar(10))
go
insert into test0314 values (1,'VF081')
insert into test0314 values (1,'VF082')
insert into test0314 values (2,'VF081')
insert into test0314 values (2,'VF082')
insert into test0314 values (3,'VF081')
insert into test0314 values (4,'VF081')
insert into test0314 values (4,'VF081')
go
select count(*) from
(select distinct customer_id from test0314 where product_code = 'VF081') as A
inner join
(select distinct customer_id from test0314 where product_code = 'VF082') as B
on A.customer_id = B.customer_id
go
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 114.38.66.235
※ 编辑: TeemingVoid 来自: 114.38.66.235 (03/14 16:21)