有如下表：
table1
batchidId Id passtimes
101 A01 1
102 A01 2
103 A02 1
104 A02 2
105 A03 1
106 A04 1
table2
batchidId Id state
101 A01 pass
102 A01 pass
103 A02 pass
106 A04 pass
table3
id
A01
A02
A03
A04
我现在要在table3中取所有id曾经在 table1,table2中出现过的，并且在table1中出现的次数与table2中次数一样的数据
希望得到的数据位
id
A01
A04
写在一条sql中，关键是where后面的不怎么会写，求高手
------解决方案--------------------
- SQL code
SELECT * FROM TABLE3WHERE (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 allselect 102,'A01',2 union allselect 103,'A02',1 union allselect 104,'A02',2 union allselect 105,'A03',1 union allselect 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 allselect 102,'A01','pass' union allselect 103,'A02','pass' union allselect 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 allselect 'A02' union allselect 'A03' union allselect 'A04'select id from table3 where id in(select a.id from(select id,COUNT(1) as times from [table1]group by id)ainner join (select id,COUNT(1) as times from [table2]group by id)b on a.Id=b.Id and a.times=b.times)/*idA01A04*/
------解决方案--------------------
- SQL code
if object_id('[table1]') is not null drop table [table1]gocreate table [table1] (batchidId int,Id nvarchar(6),passtimes int)insert into [table1]select 101,'A01',1 union allselect 102,'A01',2 union allselect 103,'A02',1 union allselect 104,'A02',2 union allselect 105,'A03',1 union allselect 106,'A04',1if object_id('[table2]') is not null drop table [table2]gocreate table [table2] (batchidId int,Id nvarchar(6),state nvarchar(8))insert into [table2]select 101,'A01','pass' union allselect 102,'A01','pass' union allselect 103,'A02','pass' union allselect 106,'A04','pass'if object_id('[table3]') is not null drop table [table3]gocreate table [table3] (id nvarchar(6))insert into [table3]select 'A01' union allselect 'A02' union allselect 'A03' union allselect 'A04'select * from [table1]select * from [table2]select * from [table3]with TTas(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 no2from table3 C)select ID from TT where no1 = no2/*A01A04
------解决方案--------------------