SELECT * FROM T1,T2 WHRERE T1.B1=T2.B1 AND T1.C1=T2.C1 AND NOT EXISTS(SELECT 1 FROM T3 WHERE T3.B1=T2.B1 AND T3.C1=T2.C1)
SELECT * FROM ( SELECT A.BankName,A.City FROM t1 AS A JOIN t2 AS B ON A.Bankname=B.BankName AND A.city=B.City ) AS A WHERE NOT EXISTS(SELECT * FROM t3 WHERE A.BankName = BankName AND A.City=City);
SELECT * FROM T1,T2 WHERE T1.B1=T2.B1 AND T1.C1=T2.C1 AND NOT EXISTS(SELECT 1 FROM T3 WHERE T3.B1=T2.B1 AND T3.C1=T2.C1)
SELECT * FROM T1 A WHERE EXISTS(SELECT 1 FROM T2 WHERE A.BANKNAME = BANKNAME AND A.CITY = CITY) AND NOT EXISTS(SELECT 1 FROM T3 WHERE A.BANKNAME = BANKNAME AND A.CITY = CITY)
SELECT * FROM T1,T2 WHRERE T1.CITY=T2.CITY AND T1.BANKNAME =T2.BANKNAME AND NOT EXISTS(SELECT 1 FROM T3 WHERE T3.BANKNAME =T2.BANKNAME AND T3.CITY=T2.CITY)
SELECT * FROM (SELECT A.BankName,A.City FROM t1 AS A ,t2 AS B WHERE A.Bankname=B.BankName AND A.city=B.City ) AS A WHERE NOT EXISTS(SELECT * FROM t3 WHERE A.BankName = BankName AND A.City=City)
( select * from t1 intersect select * from t2 ) except select * from t3
--所说用EXISTS慢,所以写个JOIN的写法 DECLARE @T1 TABLE(COL1 INT,COL2 INT) INSERT INTO @T1 SELECT 1,1 UNION ALL SELECT 2,2 DECLARE @T2 TABLE(COL1 INT,COL2 INT) INSERT INTO @T2 SELECT 1,1 UNION ALL SELECT 2,2 DECLARE @T3 TABLE(COL1 INT,COL2 INT) INSERT INTO @T3 SELECT 3,3 UNION ALL SELECT 2,2 SELECT T1.* FROM @T1 T1 INNER JOIN @T2 T2 ON T1.COL1=T2.COL1 AND T1.COL2=T2.COL2 LEFT JOIN @T3 T3 ON T1.COL1=T3.COL1 AND T1.COL2=T3.COL2 WHERE T3.COL1 IS NULL
AND T1.C1=T2.C1
AND NOT EXISTS(SELECT 1 FROM T3 WHERE T3.B1=T2.B1 AND T3.C1=T2.C1)
FROM (
SELECT A.BankName,A.City FROM t1 AS A
JOIN t2 AS B ON A.Bankname=B.BankName AND A.city=B.City
) AS A
WHERE NOT EXISTS(SELECT * FROM t3 WHERE A.BankName = BankName AND A.City=City);
T1.B1=T2.B1
AND T1.C1=T2.C1
AND NOT EXISTS(SELECT 1 FROM T3 WHERE T3.B1=T2.B1 AND T3.C1=T2.C1)
WHERE EXISTS(SELECT 1 FROM T2 WHERE A.BANKNAME = BANKNAME AND A.CITY = CITY)
AND NOT EXISTS(SELECT 1 FROM T3 WHERE A.BANKNAME = BANKNAME AND A.CITY = CITY)
AND T1.BANKNAME =T2.BANKNAME
AND NOT EXISTS(SELECT 1 FROM T3 WHERE T3.BANKNAME =T2.BANKNAME AND T3.CITY=T2.CITY)
FROM
(SELECT
A.BankName,A.City
FROM
t1 AS A ,t2 AS B
WHERE
A.Bankname=B.BankName
AND
A.city=B.City
)
AS A
WHERE
NOT EXISTS(SELECT * FROM t3 WHERE A.BankName = BankName AND A.City=City)
intersect
select * from t2
) except
select * from t3
--所说用EXISTS慢,所以写个JOIN的写法
DECLARE @T1 TABLE(COL1 INT,COL2 INT)
INSERT INTO @T1
SELECT 1,1 UNION ALL
SELECT 2,2
DECLARE @T2 TABLE(COL1 INT,COL2 INT)
INSERT INTO @T2
SELECT 1,1 UNION ALL
SELECT 2,2
DECLARE @T3 TABLE(COL1 INT,COL2 INT)
INSERT INTO @T3
SELECT 3,3 UNION ALL
SELECT 2,2
SELECT T1.* FROM @T1 T1
INNER JOIN @T2 T2 ON T1.COL1=T2.COL1 AND T1.COL2=T2.COL2
LEFT JOIN @T3 T3 ON T1.COL1=T3.COL1 AND T1.COL2=T3.COL2
WHERE T3.COL1 IS NULL