TSQL 比较两个集合 [英] TSQL Comparing two Sets
本文介绍了TSQL 比较两个集合的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当给出两组时
s1 ={ a,b,c,d} s2={b,c,d,a}
s1 ={ a,b,c,d} s2={b,c,d,a}
(即)
TableA
Item
a
b
c
d
TableB
Item
b
c
d
a
如何编写Sql查询以显示tableA和tableB中的元素相等".[不使用SP或UDF]
How to write Sql query to display "Elements in tableA and tableB are equal". [Without using SP or UDF]
输出
Elements in TableA and TableB contains identical sets
推荐答案
使用:
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
测试:
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
这篇关于TSQL 比较两个集合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文