27,582
社区成员




create table table1(row int,col int)
insert into table1(row,col)
select 1,3 union all
select 2,4 union all
select 2,3
create table table2(limit1 int,limit2 int)
insert into table2(limit1,limit2)
select 2,3
-- 方法1
select *
from table1 t1
where not exists(select 1
from table2 t2
where t2.limit1=t1.row
and t2.limit2=t1.col)
/*
row col
----------- -----------
1 3
2 4
(2 行受影响)
*/
-- 方法2
select t1.*
from table1 t1
left join table2 t2 on t1.row=t2.limit1
and t1.col=t2.limit2
where t2.limit1 is null
/*
row col
----------- -----------
1 3
2 4
(2 行受影响)
*/
CREATE TABLE #T1
(ROW INT,
COL INT)
INSERT INTO #T1
SELECT 1,3 UNION ALL
SELECT 2,4 UNION ALL
SELECT 2,3
GO
CREATE TABLE #T2
(LIMIT1 INT,
LIMIT2 INT)
INSERT INTO #T2
SELECT 2,3 UNION ALL
SELECT 1,3
GO
SELECT * FROM #T1 A
WHERE NOT EXISTS (SELECT 1 FROM #T2 WHERE A.ROW=LIMIT1 AND A.COL=LIMIT2)