Re: [SQL ] 相同欄位兩條件
※ 引述《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)
討論串 (同標題文章)