如何过滤数字并获得结果 [英] 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

推荐答案

使用除了，不在，或不存在：

Use Except, Not In, or Not 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屋！