不是在 SQL 语句中吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10132371/
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
Not in In SQL statement?
提问by James123
I have set of ids in excel around 5000 and in the table I have ids around 30000. If I use 'In' condition in SQL statment I am getting around 4300 ids from what ever I have ids in Excel. But If I use 'Not In' with Excel id. I have getting around 25000+ records. I just to find out I am missing with Excel ids in the table.
我在 excel 中设置了大约 5000 的 ID，在表中我有大约 30000 的 ID。如果我在 SQL 语句中使用“In”条件，我会从我在 Excel 中拥有的 ID 中得到大约 4300 个 ID。但是如果我在 Excel id 中使用“Not In”。我有大约 25000 多条记录。我只是发现我在表格中缺少 Excel id。
How to write sql for this?
如何为此编写sql？
Example: Excel Ids are
示例：Excel ID 是
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
Table has IDs
表有 ID
1,
2,
3,
4,
6,
8,
9,
11,
12,
14,
15
Now I want get 5,7,10values from Excel which missing the table?
现在我想5,7,10从缺少表格的 Excel 中获取值？
Update:
更新：
What I am doing is
我正在做的是
SELECT [GLID]
FROM [tbl_Detail]
where datasource = 'China' and ap_ID not in (5206896,
5206897,
5206898,
5206899,
5117083,
5143565,
5173361,
5179096,
5179097,
5179150)
采纳答案by Tim Schmelter
You're probably looking for EXCEPT:
您可能正在寻找EXCEPT：
SELECT Value
FROM @Excel
EXCEPT
SELECT Value
FROM @Table;
Edit:
编辑：
Exceptwill
Except将要
- treat NULL differently(NULL values are matching)
- apply DISTINCT
- 区别对待 NULL（NULL 值匹配）
- 申请DISTINCT
unlike NOT IN
不像 NOT IN
Here's your sample data:
这是您的示例数据：
declare @Excel Table(Value int);
INSERT INTO @Excel VALUES(1);
INSERT INTO @Excel VALUES(2);
INSERT INTO @Excel VALUES(3);
INSERT INTO @Excel VALUES(4);
INSERT INTO @Excel VALUES(5);
INSERT INTO @Excel VALUES(6);
INSERT INTO @Excel VALUES(7);
INSERT INTO @Excel VALUES(8);
INSERT INTO @Excel VALUES(9);
INSERT INTO @Excel VALUES(10);
declare @Table Table(Value int);
INSERT INTO @Table VALUES(1);
INSERT INTO @Table VALUES(2);
INSERT INTO @Table VALUES(3);
INSERT INTO @Table VALUES(4);
INSERT INTO @Table VALUES(6);
INSERT INTO @Table VALUES(8);
INSERT INTO @Table VALUES(9);
INSERT INTO @Table VALUES(11);
INSERT INTO @Table VALUES(12);
INSERT INTO @Table VALUES(14);
INSERT INTO @Table VALUES(15);
回答by Leniel Maccaferri
Try this:
尝试这个：
SELECT tableExcel.ID
FROM tableExcel
WHERE tableExcel.ID NOT IN(SELECT anotherTable.ID FROM anotherTable)
Here's an SQL Fiddle to try this: sqlfiddle.com/#!6/31af5/14
这是一个尝试此操作的 SQL Fiddle：sqlfiddle.com/#!6/31af5/14
回答by Vince Pergolizzi
Import your excel file into SQL Server using the Import Data Wizard found in SQL Server Management Studio.
使用 SQL Server Management Studio 中的导入数据向导将 Excel 文件导入 SQL Server。
Then you can write the following query to find any IDs which are in the file but not in the table:
然后您可以编写以下查询来查找文件中但不在表中的任何 ID：
SELECT id
FROM imported_table
WHERE id NOT IN (SELECT id FROM db_table)
回答by JotaBe
You should move excel data to a table in SQL Server, and then do the query in SQL Server.
您应该将excel数据移动到SQL Server中的表中，然后在SQL Server中进行查询。
select distinct id from Excel where id not in (select your ids from Sqltable)
(Obviously select your ids from Sqltable is a select which returns the Ids existing on SQL Server).
（显然，从 Sqltable 中选择您的 ID 是一个选择，它返回 SQL Server 上现有的 ID）。
You may think that moving data to SQL Server is hard to do, but, on the contrary, it's very easy:
您可能认为将数据移动到 SQL Server 很难，但恰恰相反，它非常容易：
1) create a table
1）创建一个表
CREATE TABLE ExcelIds (Id int)
2) add a new column in excel with the following formula:
2）用以下公式在excel中添加一个新列：
="insert into ExcelIds values(" & XX & ")"
where XX is the reference to the cell in the column with excel Ids.
其中 XX 是对具有 excel Id 的列中单元格的引用。
3) copy the "inserts" from Excel into SSMS or whatever tool you're usin in SQL Server, and execute them.
3) 将 Excel 中的“插入”复制到 SSMS 或您在 SQL Server 中使用的任何工具，然后执行它们。
Now you have 2 tables in SQL Server, so that querying it is absolutely easy.
现在您在 SQL Server 中有 2 个表，因此查询它绝对容易。
When you're over, just drop the table
当你结束时，把桌子放下
DROP TABLE ExcelIds
NOTE: I didn't create a key on SQL Server table because I suppose that the Ids can be repeated. Neither is justified to create a more complex SQL Query to avoid duplicates in ExcelIds for this ad hoc solution.
注意：我没有在 SQL Server 表上创建键，因为我认为 ID 可以重复。对于此临时解决方案，创建更复杂的 SQL 查询以避免 ExcelIds 中的重复都没有理由。
