SQL Server 2005 如何查找拥有所有书籍的用户 [英] SQL Server 2005 How to Find the user who has all books

问题描述

我正在尝试查询拥有所有计算机书籍的用户.例如，我有两个表:

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

1) 物品表

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

2) 用户库存表

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

由于用户1"拥有所有计算机书籍，我想进行返回用户 1 的查询.

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

我所做的是...

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..

但是，我不太确定该怎么做..因为我不能使用 count 或任何聚合.

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

不要使用计数"

无论如何知道他们如何找到拥有所有计算机书籍的用户 ID 吗?

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

推荐答案

我的解决方案的关键是 except 子句.我对所有用户/书籍的可能性进行交叉连接，然后 - 使用 except 子句 - 获取不满足这些可能性的所有用户的列表.即 A = 所有可能性，B = 实际数据，除 B 外的 A 将仅包含 A 中缺少行的用户.从那里，我查询清单表中表示的所有用户，这些用户不在子表中返回的用户中-询问.我承认这很复杂，可以使用分解成临时表.

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
)

这篇关于SQL Server 2005 如何查找拥有所有书籍的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！