当给出两个集合
s1 = {a,b,c,d} s2 = {b,c,d,a}
(即)
TableA
Item
a
b
c
d
TableB
Item
b
c
d
a
如何编写Sql查询以显示“表A和表B中的元素相等”。[不使用SP或UDF]
输出
Elements in TableA and TableB contains identical sets
TSQL比较两个集合
7
- Gopi
2
你不想将它们进行交集吗？ - Denis Valeev
顺便提一下，如果你需要在一个集合集合中搜索匹配的集合，那就是另一个问题了，这里的解决方案可能帮不上太多忙。 - Peter Radocchia
7个回答
9
使用：
SELECT CASE
WHEN COUNT(*) = (SELECT COUNT(*) FROM a)
AND COUNT(*) = (SELECT COUNT(*) FROM b) THEN 'Elements in TableA and TableB contains identical sets'
ELSE 'TableA and TableB do NOT contain identical sets'
END
FROM (SELECT a.col
FROM a
INTERSECT
SELECT b.col
FROM b) x
Test with:
WITH a AS (
SELECT 'a' AS col
UNION ALL
SELECT 'b'
UNION ALL
SELECT 'c'
UNION ALL
SELECT 'd'),
b AS (
SELECT 'b' AS col
UNION ALL
SELECT 'c'
UNION ALL
SELECT 'd'
UNION ALL
SELECT 'a')
SELECT CASE
WHEN COUNT(*) = (SELECT COUNT(*) FROM a)
AND COUNT(*) = (SELECT COUNT(*) FROM b) THEN 'yes'
ELSE 'no'
END
FROM (SELECT a.col
FROM a
INTERSECT
SELECT b.col
FROM b) x
- OMG Ponies
4
1子树成本的信息非常有趣。 - Denis Valeev
那就是每个表2次读取+1次连接。难道没有人知道如何使用“FULL JOIN”吗？ - Peter Radocchia
@Peter：我运行了你的FULL JOIN选项，发现在SS2005上使用INTERSECT或EXCEPT与FULL JOIN之间有一个极大的性能差距。我想INTERSECT（相对于Nix的EXCEPT版本）略微更高的成本是由于计算数量以确保正确的消息显示。 - OMG Ponies
@Emtucifier：基于子树成本值，完全连接更糟糕-请参见每个答案的相应评论以获取详细信息。 - OMG Ponies
7
使用FULL JOIN，可以像这样：
SELECT
CASE
WHEN EXISTS (
SELECT * FROM s1 FULL JOIN s2 ON s1.Item = s2.Item
WHERE s1.Item IS NULL OR s2.Item IS NULL
)
THEN 'Elements in tableA and tableB are not equal'
ELSE 'Elements in tableA and tableB are equal'
END
这种方法的优点在于第一个不匹配就会短路，而其他解决方案需要对每个表进行两次完整扫描（一次用于COUNT(*)，一次用于JOIN/INTERSECT）。
预计成本显著低于其他解决方案。
- Peter Radocchia
4
我的初始方法。我猜这不够有趣！ :) - Denis Valeev
啊，来晚了，全连接也是我的答案。 - ErikE
@Emtucifor：这是一场多么盛大的派对啊！一定是我需要分散注意力的时间。 - Peter Radocchia
3
我的怪物:
;with SetA as
(select 'a' c union
select 'b' union
select 'c')
, SetB as
(select 'b' c union
select 'c' union
select 'a' union
select 'd'
)
select case (select count(*) from (
select * from SetA except select * from SetB
union
select * from SetB except select * from SetA
)t)
when 0 then 'Equal' else 'NotEqual' end 'Equality'
- Denis Valeev
3
子树成本为0.0178567——比cmsjr的答案稍微好一点。 - OMG Ponies
我的AEP显示STC为0.01129。但是，我只是整个查询运行，没有事先准备那些集合。 - Denis Valeev
我运行两次检查查询是否针对错误和正样本返回有效输出。如果查询未通过初步测试，我不会发布成本。 - OMG Ponies
3
注意，我将使用交叉连接（Cross Join）。
Declare @t1 table(val varchar(20))
Declare @t2 table(val varchar(20))
insert into @t1 values ('a')
insert into @t1 values ('b')
insert into @t1 values ('c')
insert into @t1 values ('d')
insert into @t2 values ('c')
insert into @t2 values ('d')
insert into @t2 values ('b')
insert into @t2 values ('a')
select
case when
count(1) =
(((Select count(1) from @t1)
+ (Select count(1) from @t2)) / 2.0)
then 1 else 0 end as SetsMatch from
@t1 t1 cross join @t2 t2
where t1.val = t2.val
- cmsjr
15
1@Peter，我不知道你在说什么。 - cmsjr
@OMG，我明确删除了那条评论，我不希望你回复它。我的新评论是这样的。 - cmsjr
1OMG @OMG，你的乐趣在哪里啊。 - cmsjr
1"a cross join b where a.val = b.val" 等同于 "a inner join b on a.val = b.val"，并且会被优化器重写为内连接。检查执行计划，它们将是相同的。 - Peter Radocchia
那么这个角度怎么样？既然所有变量都归结为相同的执行计划，而我的实际意图是将所有值与所有其他值进行比较，难道交叉连接不比内连接或没有连接运算符更明确地表达了这个意图吗？ - cmsjr
显示剩余10条评论
1
可以使用EXCEPT和CASE来实现
select
case
when count (1)=0
then 'Elements in TableA and TableB contains identical sets'
else 'Nope' end from (
select item from s1
EXCEPT
select item from s2
) b
- Nix
5
2s2 可能比 S1 包含更多的项，但是这个查询会声称它们是相同的。 - Peter Radocchia
有人会认为这些集合仍然是相同的，如果你想的话，你可以断言计数，或者如果你真的很无聊，你可以做行校验和。 - Nix
在SQL Server 2005上，这个查询的子树成本比我使用INTERSECT的版本更好，为0.0000459（我的版本为0.0000702）。我想我的版本成本更高是由于COUNT比较造成的。 - OMG Ponies
1此查询不正确。如果s2具有与s1匹配的所有行，但具有额外的不匹配行，则此查询仍将返回它们具有相同的集合。 - ErikE
-1 -- 我同意@Peter和@Emtucifor的观点，这会产生错误的结果。 - onedaywhen
1
由于这个帖子对我非常有用，所以我想分享一下我的解决方案。
我遇到了一个类似的问题，比这个具体的单集比较更普遍适用。我试图找到一个元素的 ID，该元素拥有一组与查询多元素项匹配的子元素集。
相关的模式信息如下：
table events, pk id
table solutions, pk id, fk event_id -> events
table solution_sources, fk solutionid -> solutions
columns unitsourceid, alpha
查询：查找事件ID为110的解决方案，其中解决方案来源集合与ss_tmp中匹配(unitsourceid, alpha)的集合相匹配。（我认为这也可以在没有tmp表的情况下完成。）
解决方案：
with solutionids as (
select y.solutionid from (
select ss.solutionid, count(ss.solutionid) x
from solutions s, solution_sources ss
where s.event_id = 110 and ss.solutionid = s.id
group by ss.solutionid
) y where y.x = ( select count(*) from ss_tmp )
)
select solutionids.solutionid from solutionids where
(
select case
when count(*) = ( select count(*) from ss_tmp ) then true
else false
end
from
( SELECT unitsourceid, alpha FROM solution_sources
where solutionid = solutionids.solutionid
INTERSECT
SELECT unitsourceid, alpha FROM ss_tmp ) x
)
针对一个包含4个项目的测试查询和一个具有匹配解决方案（具有相同数量的子元素，每个子元素都匹配）的测试数据库进行了测试，其中包括几个完全不匹配的解决方案，以及1个具有3个匹配子元素的解决方案，1个具有所有4个匹配子元素加上一个额外子元素的解决方案，以及1个具有4个子元素中有3个与查询匹配的解决方案。只返回真正匹配的ID。
非常感谢 -Linus
- user1978162
0
使用EXCEPT语句
使用EXCEPT语句测试两个集合是否包含相同的行时，需要在两个方向上执行EXCEPT（A EXCEPT B 和 B EXCEPT A）。如果任何一个比较返回记录，则表示这些集合不同。如果两个比较都没有返回记录，则它们相同。
这个好处是可以对任意数量的特定列进行比较，并且处理NULL值时会隐式地进行处理，而无需跳过 hoops 进行比较。
这样做的一个好的使用案例是验证保存记录集是否正确，特别是当影响现有记录集时。
SELECT IsMatching = (1 ^ convert(bit, count(*)))
FROM (
SELECT Mismatched = 1 -- Can be any column name
FROM (
SELECT Item -- Can have additional columns
FROM TableA
EXCEPT
SELECT Item -- Can have additional columns
FROM TableB
) as A
UNION
SELECT Mismatched = 1 -- Can be any column name
FROM (
SELECT Item -- Can have additional columns
FROM TableB
EXCEPT
SELECT Item -- Can have additional columns
FROM TableA
) as A
) as A
- Thomas Phaneuf
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接