从一个表中查找“不”在另一个表的分组中的值，以及哪个组缺少该值？

发布于 2024-08-28 06:02:41 字数 1045 浏览 5 评论 0原文

我希望我没有在这里遗漏一些非常简单的东西。我已经进行了 Google 搜索并通过 Stack Overflow 进行了搜索。

情况如下：为了简单起见，假设我在 SQL Server 2008 数据库中有一个名为“PeoplesDocs”的表，其中包含一群人和他们拥有的所有文档。所以一个人可以拥有多份文件。我还有一个名为“RequiredDocs”的表，它只包含一个人应该拥有的所有文档。下面是它的样子：

PeoplesDocs：

PersonID   DocID  
--------   -----  
1          A  
1          B  
1          C  
1          D  
2          C  
2          D  
3          A  
3          B  
3          C

RequiredDocs：

DocID     DocName  
-----     ---------  
A         DocumentA  
B         DocumentB  
C         DocumentC  
D         DocumentD

如何编写一个返回以下内容的 SQL 查询：

PersonID   MissingDocs  
--------   -----------  
2          DocumentA  
2          DocumentB  
3          DocumentD

我已经尝试过，并且我的大部分搜索都指向类似的内容：

SELECT DocID  
FROM DocsRequired  
WHERE NOT EXIST IN (  
SELECT DocID FROM PeoplesDocs)

但显然这不会返回任何内容这个例子是因为每个人都至少有一个文档。

此外，如果某人没有任何文档，那么 PeoplesDocs 表中将有一条记录的 DocID 设置为 NULL。

原文

I hope I am not missing something very simple here. I have done a Google search(es) and searched through Stack Overflow.

Here is the situation: For simplicity's sake let's say I have a table called "PeoplesDocs", in a SQL Server 2008 DB, that holds a bunch of people and all the documents that they own. So one person can have several documents. I also have a table called "RequiredDocs" that simply holds all the documents that a person should have. Here is sort of what it looks like:

PeoplesDocs:

PersonID   DocID  
--------   -----  
1          A  
1          B  
1          C  
1          D  
2          C  
2          D  
3          A  
3          B  
3          C

RequiredDocs:

DocID     DocName  
-----     ---------  
A         DocumentA  
B         DocumentB  
C         DocumentC  
D         DocumentD

How do I write a SQL query that returns some variation of:

PersonID   MissingDocs  
--------   -----------  
2          DocumentA  
2          DocumentB  
3          DocumentD

I have tried, and most of my searching has pointed to, something like:

SELECT DocID  
FROM DocsRequired  
WHERE NOT EXIST IN (  
SELECT DocID FROM PeoplesDocs)

but obviously this will not return anything in this example because everyone has at least one of the documents.

Also, if a person does not have any documents then there will be one record in the PeoplesDocs table with the DocID set to NULL.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半城柳色半声笛 2024-09-04 06:02:42

像这样的事情怎么样：

Select ...
From RequiredDocs As RD
    Cross Join People As P
Where Not Exists(
                Select 1
                From PeoplesDocs As PD1
                Where PD1.PersonId = P.PersonId
                    And PD1.DocId = RD.DocId
                )

How about something like this:

Select ...
From RequiredDocs As RD
    Cross Join People As P
Where Not Exists(
                Select 1
                From PeoplesDocs As PD1
                Where PD1.PersonId = P.PersonId
                    And PD1.DocId = RD.DocId
                )

回复收藏 0 原文

陪你到最终 2024-09-04 06:02:42

SELECT
    p.PersonID,
    rd.DocName AS MissingDocs
FROM
    dbo.People p, dbo.RequiredDocs rd
WHERE
    rd.DocID NOT IN (SELECT pd.DocID FROM dbo.PeoplesDocs pd
        WHERE pd.PersonID = p.PersonID)

SELECT
    p.PersonID,
    rd.DocName AS MissingDocs
FROM
    dbo.People p, dbo.RequiredDocs rd
WHERE
    rd.DocID NOT IN (SELECT pd.DocID FROM dbo.PeoplesDocs pd
        WHERE pd.PersonID = p.PersonID)

回复收藏 0 原文

~没有更多了~