比较两组数据 [英] Comparing two sets of data
问题描述
我有以下情况。一组数据有274行（set2）
而花药有264（set1）。两个数据集的结构类似于
以及它们的值都是来自同一父级
表的提取。希望这些信息能代替DDL。我需要找到这两组之间的间隙
行。
尝试运行类似
的查询
select count（*）
来自set2
哪里不存在
（从set1中选择*
）
没有产生我想要的东西。还有什么可以尝试的？
TIA。
I have the following situation. One set of data has 274 rows (set2)
and anther has 264 (set1). Both data sets are similar in structure as
well as values for both of them were extracts from the same parent
table. Hope the info would substitute DDL. I need to find the "gap"
rows between these two sets.
Attempted to run a query like
select count(*)
from set2
where not exists
(select *
from set1)
did not yield what I desired. What else to try?
TIA.
推荐答案
NickName写道：
NickName wrote:
我有以下情况。一组数据有274行（set2）
而另一组有264（set1）。两个数据集的结构相似，以及它们的值都是来自同一父表的提取物。
希望信息可以替代DDL。
它没有。
我需要找到这两组之间的间隙行。
尝试运行一个类似
的查询从set2中选择count（*）
哪里不存在
（从set1中选择*
）
没有产生我的东西期望。还有什么可以尝试？
I have the following situation. One set of data has 274 rows (set2)
and anther has 264 (set1). Both data sets are similar in structure as
well as values for both of them were extracts from the same parent
table.
Hope the info would substitute DDL.
It doesn''t.
I need to find the "gap"
rows between these two sets.
Attempted to run a query like
select count(*)
from set2
where not exists
(select *
from set1)
did not yield what I desired. What else to try?
尝试发布您的DDL。我们至少需要列名和键来帮助你在这里获得
。
Zach
Try posting your DDL. We at least need column names and keys to help you
here.
Zach
OK ，
我已经证明了EXISTS关键字/功能无法解决这个问题。\\ b
问题。但那又怎么样？
- 测试两个数据集之间的相等性
- 期望的结果集：返回缺口行
- DDL和DML
创建表#tmp1（col1 int，col2 char（1））;
插入＃tmp1
值（1，''a''）
插入＃tmp1
值（2，' 'b''）
插入＃tmp1
值（3，''c''）
插入＃tmp1
值（4，''d''）
插入＃tmp1
值（5，''e''）;
从＃tmp1中选择*到＃tmp2
其中col1< 5;
选择*
来自＃tmp1
哪里不存在
（选择*
$来自＃tmp2的b $ b）
OK,
I''ve proven that the "EXISTS" keyword/function can''t solve this
problem. But then what?
-- test equality between two data sets
-- desired resultset: return "gap" rows
-- DDL and DML
create table #tmp1 (col1 int, col2 char(1));
insert into #tmp1
values(1,''a'')
insert into #tmp1
values(2,''b'')
insert into #tmp1
values(3,''c'')
insert into #tmp1
values(4,''d'')
insert into #tmp1
values(5,''e'');
select * into #tmp2
from #tmp1
where col1 < 5;
select *
from #tmp1
where not exists
(select *
from #tmp2)
NickName（da****@rock.com）写道：
NickName (da****@rock.com) writes:
我'已经证明了EXISTS关键字/功能无法解决这个问题。但那又怎么样？
I''ve proven that the "EXISTS" keyword/function can''t solve this
problem. But then what?
当然可以，但是你必须指定NOT EXISTS的工作方式。
SQL与心灵感应无关。
对于你的复制品，你可以从＃tmp1 t1
$中选择* b / b $ b / b
b $ b不存在（从＃tmp2 t2选择*
WHERE t1.col1 = t2.col1）
或者
选择*
来自＃tmp1 t1
哪里不存在（选择*
来自＃tmp2 t
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2）
取决于你在寻找什么。第一个只列出缺少的键
值，第二个列表所有不匹配。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3联机丛书 http://www.microsoft.com/sql /techinf...2000/books.asp
It certainly can, but you must specify how the NOT EXISTS is to work.
SQL is not about telepathy.
For your repro, you could do
select *
from #tmp1 t1
where not exists (Select *
from #tmp2 t2
WHERE t1.col1 = t2.col1)
or
select *
from #tmp1 t1
where not exists (Select *
from #tmp2 t
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2)
depending on what you are looking for. The first just lists missing key
values, the second list all mismatches.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
这篇关于比较两组数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
