SQL query skills exclusive query is a customer who has used a certain item

The sales table is as follows:

Customer	Product
A	1
B	2
B	1
C	2
D	1
E	2

Ask the customer who has used only 2 products (no other products used) for exclusive Query

Table Structure

 

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'

Query;

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

Result

Customer times
---------------------
C 3
E 1

(Two rows have been processed successfully)

Other methods

 

Select customer,
Count (*) times
From @ TB
Where customer not in (select customer
From @ TB
Where product! = 2)
Group by customer

 

Organized from http://community.csdn.net/Expert/topic/5342/5342039.xml? Temp =. 7104914.

Thank you.Hhhdyj (Firefly)