table1(aa,bb,cc),
11,22,33
01,02,03
01,04,05
table2(aa,bb,cc)
10,22,33
01,02,04
02,04,05我想得到的是把table1和table2中aa,bb列看着是一个组合列来查询--比如我想得到vtable2中除掉第2行的数据。
也就是得到的就是下面这个语句相反的结果。
select b.* from table1 a,table2 b where a.aa=b.aa and a.bb=b.bb
11,22,33
01,02,03
01,04,05
table2(aa,bb,cc)
10,22,33
01,02,04
02,04,05我想得到的是把table1和table2中aa,bb列看着是一个组合列来查询--比如我想得到vtable2中除掉第2行的数据。
也就是得到的就是下面这个语句相反的结果。
select b.* from table1 a,table2 b where a.aa=b.aa and a.bb=b.bb
(
select b.* from table1 a,table2 b where a.aa=b.aa and a.bb=b.bb
) tb
where ta.aa<>tb.aa and ta.bb<>tb.bb
insert into @table1 select '11','22','33'
union all select '01','02','03'
union all select '01','04','05'
declare @table2 table(aa varchar(10),bb varchar(10), cc varchar(10))
insert into @table2 select '10','22','33'
union all select '01','02','04'
union all select '02','04','05'select ta.* from @table2 ta,
(
select b.* from @table1 a,@table2 b where a.aa=b.aa and a.bb=b.bb
) tb
where ta.aa<>tb.aa and ta.bb<>tb.bb/*
aa bb cc
---------- ---------- ----------
10 22 33
02 04 05(所影响的行数为 2 行)
*/
SELECT * FROM Table2
EXCEPT
SELECT * FROM Table1
可以把aa, bb拼接起來, 作為一個欄位處理.
SELECT * FROM Table2
WHERE aa+bb NOT IN
(
SELECT aa+bb FROM Table1
)
SELECT * FROM Table2
EXCPT
(
SELECT T2.* FROM Table1 T1, Table2 T2
WHERE T1.aa=T2.aa AND T1.bb=T2.bb
) AS A
或
;WITH A AS
(
SELECT T2.* FROM Table1 T1, Table2 T2
WHERE T1.aa=T2.aa AND T1.bb=T2.bb
)
SELECT * FROM Table2
EXCEPT
SELECT * FROM A
当数据量大的时候,有些耗时间,arlin_hou(風信子) 这个不错
SELECT * FROM Table2
WHERE aa+bb NOT IN
(
SELECT aa+bb FROM Table1
)