判断出现次数是否相同,该怎么解决

判断出现次数是否相同
有如下表：
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
[解决办法]
探讨


引用:

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……