SQL Server 2005 How to Find the user who has all b

问题:

I am trying to make the query that user who has all computer book. For example, I have two tables:

1) Item table

|itemid |item_name          |
------------------------------
|1      | computerbook1     |    
|2      | computerbook2     |    
|3      | computerbook3     |
|4      | mathbook1         |    
|5      | mathbook2         |    
|6      | physicsbook       | 

2) userinventory table

|used_id | name_item        |
-----------------------------
|1       | computerbook1    |    
|1       | computerbook2    |    
|1       | computerbook3    |    
|2       | computerbook1    |    
|2       | mathbook1        |    
|2       | physicsbook      |    
|3       | computerbook1    |    
|3       | computerbook3    |

Since user "1" has all of computer book, I would like make query that returning the user 1.

What I did was...

Create Table #tmp (
    Classname [varchar](50) NOT NULL
)

INSERT INTO #tmp
SELECT DISTINCT item_name  
  FROM ITEM
 WHERE item_name  like 'computerbook%'

I would like to compare to find user who has all of item..

However, I am not really sure how to do it.. because I can not use count or any aggregation.

Do not use "count"

Does anyway know they way to find user id who has all of computer book?

回答1:

The key to my solution is the except clause. I make a cross join of all user/book possibilities, and then - with the except clause - get a list of all users who do not satisfy those possibilities. That is A = all possibilities, B = actual data, and A except B will only contain users who are missing rows in A. From there, I query for all users represented in the inventory table who aren't among those returned in the sub-query. I admit it's pretty convoluted, and could use break down into temp tables.

SELECT user_id
FROM   userinventory
EXCEPT
(
  SELECT user_id
  FROM  (
        SELECT UX.user_id, II.item_name 
        FROM item II, (SELECT UU.user_id FROM userinventory UU) UX
        WHERE II.item_name LIKE 'computerbook%'
        EXCEPT
        SELECT UU.user_id, UU.name_item
        FROM   userinventory UU
        ) XX
)

回答2:

Something like this:

SELECT DISTINCT userid FROM userinventory WHERE NOT EXISTS (SELECT null
    FROM item WHERE NOT EXISTS (SELECT null FROM userinventory i2
      WHERE i2.name_item = item.item_name AND i2.user_id = userinventory.userid))

In other words, we're looking for distinct users who own at least one item where there exists no item that they don't own.

回答3:

declare @Item table(itemid int, item_name varchar(30))
insert @item values(1, 'computerbook1')
insert @item values(2,'computerbook2')
insert @item values(3,'computerbook3')
insert @item values(4,'mathbook1')
insert @item values(5,'mathbook2')
insert @item values(6,'physicsbook')

declare @userinventory table(user_id int, name_item varchar(30))
insert @userinventory values(1,'computerbook1')
insert @userinventory values(1,'computerbook2')
insert @userinventory values(1,'computerbook3')
insert @userinventory values(2,'computerbook1')
insert @userinventory values(2,'mathbook1')
insert @userinventory values(2,'physicsbook')
insert @userinventory values(3,'computerbook1')
insert @userinventory values(3,'computerbook3')

;with users as
(
  select distinct user_id from @userinventory
), books as
(
  select item_name from @item
  where item_name like 'computerbook%'
), missingbooks as
(
  select * from users cross join books
  except 
  select user_id, name_item from @userinventory
)
select user_id from users
where user_id not in (select user_id from missingbooks)