销售表如下
customer
product
A
1
B
2
B
1
C
2
D
1
E
2
求只使用过2产品的顾客( 没有使用过其他产品) 排他查询
表结构
DECLARE @tb TABLE ( [ customer ] varchar ( 10 ), [ product ] varchar ( 10 )) INSERT INTO @tb SELECT ' A ' , ' 1 ' UNION ALL SELECT ' B ' , ' 2 ' UNION ALL SELECT ' B ' , ' 1 ' UNION ALL SELECT ' C ' , ' 2 ' UNION ALL SELECT ' C ' , ' 2 ' UNION ALL SELECT ' D ' , ' 1 ' UNION ALL SELECT ' E ' , ' 2 ' UNION ALL SELECT ' C ' , ' 2 '
查询;
SELECT customer, Count ( product) times FROM @tb t WHERE EXISTS ( SELECT 1 FROM @tb WHERE customer = t.customer AND product = 2 ) GROUP BY customer HAVING Count ( DISTINCT product) = 1结果
customer times -- -------- ----------- C 3 E 1 ( 2 行処理されました)其他方法
SELECT customer, Count ( * ) times FROM @tb WHERE customer NOT IN ( SELECT customer FROM @tb WHERE product != 2 ) GROUP BY customer
整理自http://community.csdn.net/Expert/topic/5342/5342039.xml?temp=.7104914
谢谢hhhdyj(萤火虫)