sql - TSQL比较两个集合

当给出两套时

s1 = {a，b，c，d} s2 = {b，c，d，a}

(IE)

TableA

Item
a
b
c
d

TableB

Item
b
c
d
a

如何编写Sql查询以显示“tableA和tableB中的元素相等”。 [不使用SP或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

关于sql - TSQL比较两个集合，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3661766/