※ 引述《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笔
: 谢谢
CREATE TABLE #TEMP (customer_id int, product_code varchar(10))
INSERT INTO #TEMP VALUES (1,'VF081')
……
INSERT INTO #TEMP VALUES (4,'VF081')
SELECT customer_id FROM (
SELECT DISTINCT customer_id ,product_code
FROM #TEMP
WHERE product_code in ('VF081' ,'VF082')
) AS RESULT
GROUP BY customer_id
HAVING COUNT(customer_id) = 2;
先排除重复资料 => 同 customer_id 与 product_code ,
再列出符合条件 => COUNT(customer_id) = 2 。
如果 product_id 很多个, WHERE 后面删掉并调整 COUNT 条件也可。