如何过滤数字并获得结果 [英] how do I filter numbers and get the result
问题描述
嗨
我有两套连串nos说set1由1,2,3,5,6,9,8,7,2,5,55,5656,5565,21组成,...和set2由5,6,3,8,9,4,55,3,2,4,7,8,5,1
$ b $组成b现在我如何获得不在set1中的set2数字。可以帮助sql查询
Hi
I have two sets of serial nos say set1 consists of 1,2,3,5,6,9,8,7,2,5,55,5656,5565,21,...and set2 consists of 5,6,3,8,9,4,55,3,2,4,7,8,5,1
Now how do i get the set2 numbers which are not in set1..can help with sql query
推荐答案
使用除了
,不在
,或不存在
:
UseExcept
,Not In
, orNot Exists
:
-- Except:
SELECT SerialNumber FROM Set2
EXCEPT
SELECT SerialNumber FROM Set1
-- Not In:
SELECT SerialNumber
FROM Set2
WHERE SerialNumber Not In
(
SELECT SerialNumber
FROM Set1
)
-- Not Exists:
SELECT SerialNumber
FROM Set2
WHERE Not Exists
(
SELECT 1
FROM Set1
WHERE Set1.SerialNumber = Set2.SerialNumber
)
SQL Server中的EXCEPT vs NOT IN [ ^ ]
试试这个...
创建两个临时表来读取数据
try this...
create two temp tables to read data
DROP TABLE #NotePad1
DROP TABLE #NotePad2
CREATE TABLE #NotePad1 ( serial VARCHAR(MAX))
CREATE TABLE #NotePad2 ( serial VARCHAR(MAX))
从笔记本1读取
(仅当你在拥有你文件的服务器文件夹中有读取权限时)
read from note pad1 (only if u have read permission in servers folder having ur file)
BULK INSERT #NotePad1
FROM 'ur file path1'
WITH (ROWTERMINATOR = ',')
GO
从note pad2读取(示例文件路径'\\KS50 \Query\test\2.txt'其中KS50是服务器名称,Query是共享文件夹,2.txt是文件)
read from note pad2 (sample file path '\\KS50\Query\test\2.txt' where KS50 is server name,Query is shared folder,2.txt is file)
BULK INSERT #NotePad2
FROM 'ur file path2'
WITH (ROWTERMINATOR = ',')
GO
选择#NotePad2不在#NotePad1中的序列号
Selecting #NotePad2 serial's which are not in #NotePad1
SELECT serial FROM #NotePad2
EXCEPT
SELECT serial FROM #NotePad1
来自 http://www.sqlusa.com/bestpractices2005/notepad /
祝你好运; - )
credits http://www.sqlusa.com/bestpractices2005/notepad/
good luck ;-)
这篇关于如何过滤数字并获得结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!