如何“减去”sql 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1373833/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I 'subtract' sql tables?
提问by swolff1978
Its not really a subtraction I'm looking for. And I know its not a union or intersection... I have been given a long and complex stored procedure that returns a table of active and inactive documents. I have also been given a similar stored procedure that returns another table that contains only the active documents.
它并不是我真正要寻找的减法。而且我知道它不是联合或交集......我得到了一个长而复杂的存储过程，它返回一个活动和非活动文档表。我还得到了一个类似的存储过程，它返回另一个只包含活动文档的表。
How could I get a table of inactive documents using these two store procedures?
如何使用这两个存储过程获取非活动文档表？
We are using SQL Server 2005.
我们使用的是 SQL Server 2005。
回答by LBushkin
The set operation you are looking for is called MINUS, but in SQL Server the keyword is EXCEPT
您要查找的设置操作称为 MINUS，但在 SQL Server 中关键字是 EXCEPT
SELECT ... // all documents
EXCEPT
SELECT ... // active documents
I believe that the EXCEPT set operation became available in SQL Server 2005.
我相信 EXCEPT set 操作在 SQL Server 2005 中可用。
回答by Carl Manaster
Assuming there are unique IDs that correspond across the two tables:
假设有两个表对应的唯一 ID：
select * from table_both b
where not exists (select * from table_active a where a.id = b.id)
回答by gbn
All good answers, but missing one point: The questioner (OP) has stored procedures...
所有好的答案，但缺少一点：提问者（OP）有存储过程......
You have to define temporary tables (based on your platform) to load the data
您必须定义临时表（基于您的平台）来加载数据
INSERT ...
EXEC getActive
INSERT ...
EXEC getInactive
Thenuse EXCEPT/EXISTS/MINUS/IN/OUTER JOIN/etc...
然后使用 EXCEPT/EXISTS/MINUS/IN/OUTER JOIN/etc...
回答by Jason S
SELECT * FROM Table1
LEFT JOIN Table2 on Table1.id = Table2.id
WHERE Table2.id IS NULL
this should work on almost any database engine
这应该适用于几乎所有数据库引擎
回答by tsilb
select * from MyTable1
where MyTable1.Field1 not in (
select Field1 from MyTable2)
回答by Dave Pirotte
I believe EXCEPT is what you are looking for. Syntax is similar to UNION or INTERSECT.
我相信 EXCEPT 是您正在寻找的。语法类似于 UNION 或 INTERSECT。
回答by Pablo Santa Cruz
回答by Michael Krelin - hacker
SELECT both.*
FROM both LEFT OTUER JOIN inactives USING (whatever_id)
WHERE inactives.whatever_id IS NULL;
or
或者
SELECT * FROM both
EXCEPT
SELECT * FROM inactives;
回答by Matthew Jones
In MS TSql, I think you want the EXCEPT keyword.
在 MS TSql 中，我认为您需要 EXCEPT 关键字。
query1 EXCEPT query2
Which will return all rows found in the first query that are not also found in the second query.
这将返回在第一个查询中找到但在第二个查询中找不到的所有行。
回答by Ed B
You can also do this with the NOT INclause
你也可以用NOT IN子句做到这一点
For example, assuming the stored procedures have given you table variables called @AllDocumentsand @ActiveDocumentsand each document has an identifier column called DocId
例如，假设存储过程为您提供了名为@AllDocuments和 的表变量，@ActiveDocuments并且每个文档都有一个名为的标识符列DocId
SELECT * FROM @AllDocuments
WHERE DocId NOT IN
(SELECT DocId FROM @ActiveDocuments)
Adapt this as appropriate to match your table / column names.
根据需要调整它以匹配您的表/列名称。
