SELECT * FROM TABLE3
WHERE (SELECT COUNT(*) FROM TABLE1 WHERE ID = A.ID) =
(SELECT COUNT(*) FROM TABLE2 WHERE ID = A.ID)

------解决方案--------------------
SQL code

--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1]([batchidId] int,[Id] varchar(3),[passtimes] int)
insert [table1]
select 101,'A01',1 union all
select 102,'A01',2 union all
select 103,'A02',1 union all
select 104,'A02',2 union all
select 105,'A03',1 union all
select 106,'A04',1
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
create table [table2]([batchidId] int,[Id] varchar(3),[state] varchar(4))
insert [table2]
select 101,'A01','pass' union all
select 102,'A01','pass' union all
select 103,'A02','pass' union all
select 106,'A04','pass'
--> 测试数据：[table3]
if object_id('[table3]') is not null drop table [table3]
create table [table3]([id] varchar(3))
insert [table3]
select 'A01' union all
select 'A02' union all
select 'A03' union all
select 'A04'

select id from table3 where id in(
select a.id from(
select id,COUNT(1) as times from [table1]
group by id)a
inner join (
select id,COUNT(1) as times from [table2]
group by id)b on a.Id=b.Id and a.times=b.times)

/*

id
A01
A04*/

------解决方案--------------------
SQL code
if object_id('[table1]') is not null drop table [table1]
go
create table [table1] (batchidId int,Id nvarchar(6),passtimes int)
insert into [table1]
select 101,'A01',1 union all
select 102,'A01',2 union all
select 103,'A02',1 union all
select 104,'A02',2 union all
select 105,'A03',1 union all
select 106,'A04',1

if object_id('[table2]') is not null drop table [table2]
go
create table [table2] (batchidId int,Id nvarchar(6),state nvarchar(8))
insert into [table2]
select 101,'A01','pass' union all
select 102,'A01','pass' union all
select 103,'A02','pass' union all
select 106,'A04','pass'

if object_id('[table3]') is not null drop table [table3]
go
create table [table3] (id nvarchar(6))
insert into [table3]
select 'A01' union all
select 'A02' union all
select 'A03' union all
select 'A04'

select * from [table1]
select * from [table2]
select * from [table3]


with TT
as(
select ID,(select COUNT(1) from table1 A where A.Id = C.ID group by A.id) as no1,
(select COUNT(1) from table2 B where B.Id = C.ID group by B.id) as no2
from table3 C)

select ID from TT where no1 = no2

/*
A01
A04

------解决方案--------------------
探讨


引用:

SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'table1')
BEGIN
DROP TABLE table1
END
GO
CREATE TABLE table1
(
batchidId INT,
Id VARCHAR(10),
passtimes INT
)
I……