如果标志列为id,则: select * from A where exists(select 1 from B where id=A.id)
select * from tb b where exists (select 1 from ta a where a.id=b.id and a.name=b.name .....)
DECLARE @a TABLE (id int) INSERT INTO @a SELECT 1 UNION ALL SELECT 2 DECLARE @b TABLE (id int) INSERT INTO @b SELECT 2 UNION ALL SELECT 3SELECT * FROM @a WHERE id in (SELECT id FROM @b)
--#1 SELECT field1, field2, field3 FROM ( SELECT * FROM A UNION ALL SELECT * FROM B ) a GROUP BY field1, field2, field3 HAVING COUNT(*) > 1 --#2 SELECT * FROM A WHERE EXISTS(SELECT TOP(1) 1 FROM B WHERE field1 = A.field1 AND field2 = a.field2 AND field3 = A.field3)
Select * From ATable INTERSECT Select * From Btable利用INTERSECT 取交集 最简单了
CREATE TABLE A ( id INT, age int ) INSERT INTO A SELECT 1, 50 UNION ALL SELECT 2, 9CREATE TABLE B ( id INT, age INT ) INSERT INTO B SELECT 1, 50SELECT * FROM A EXCEPT SELECT * FROM ( SELECT * FROM A EXCEPT SELECT * FROM B ) T
方法1.select * from A where (A表所有字段) in (select B表所有字段 from B);方法2.select * from A T left join B T1 on T.A表主键=T1.B表主键 /*(所有主键)*/ where T1.主键 (某一个主键既可) is not null;
select * from A where exists(select 1 from B where id=A.id)
(select 1 from ta a where a.id=b.id and a.name=b.name .....)
DECLARE @a TABLE (id int)
INSERT INTO @a
SELECT 1 UNION ALL
SELECT 2 DECLARE @b TABLE (id int)
INSERT INTO @b
SELECT 2 UNION ALL
SELECT 3SELECT * FROM @a WHERE id in (SELECT id FROM @b)
SELECT field1, field2, field3 FROM
(
SELECT * FROM A
UNION ALL
SELECT * FROM B
) a
GROUP BY field1, field2, field3
HAVING COUNT(*) > 1
--#2
SELECT * FROM A
WHERE EXISTS(SELECT TOP(1) 1 FROM B WHERE field1 = A.field1 AND field2 = a.field2 AND field3 = A.field3)
Select * From ATable
INTERSECT
Select * From Btable利用INTERSECT 取交集 最简单了
(
id INT,
age int
)
INSERT INTO A
SELECT 1, 50 UNION ALL
SELECT 2, 9CREATE TABLE B
(
id INT,
age INT
)
INSERT INTO B
SELECT 1, 50SELECT * FROM A
EXCEPT
SELECT * FROM
(
SELECT * FROM A
EXCEPT
SELECT * FROM B
) T
on T.A表主键=T1.B表主键 /*(所有主键)*/
where T1.主键 (某一个主键既可) is not null;