判断出现次数是否相同

[风一样的大叔]

有如下表：
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后面的不怎么会写，求高手

[昵称被占用了]

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

这是不考虑性能的

[开着拖拉机泡妞]

--> 测试数据：[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*/

[孤独加百列]

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
	)
	INSERT INTO table1 
SELECT 101, 'A01', 1 UNION 
SELECT 102, 'A01', 2 UNION 
SELECT 103, 'A02', 1 UNION 
SELECT 104, 'A02', 2 UNION 
SELECT 105, 'A03', 1 UNION 
SELECT 106, 'A04', 1
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'table2')
BEGIN
	DROP TABLE table2
END
GO
CREATE TABLE table2
(
	batchidId INT,
	Id VARCHAR(10),
	[state] VARCHAR(10)
)
GO
INSERT INTO table2
SELECT 101, 'A01', 'pass' UNION
SELECT 102, 'A01', 'pass' UNION
SELECT 103, 'A02', 'pass' UNION
SELECT 106, 'A04', 'pass'
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'table3')
BEGIN
	DROP TABLE table3
END
GO
CREATE TABLE table3
(
	id VARCHAR(10)
)
INSERT INTO table3  
SELECT 'A01' UNION  
SELECT 'A02' UNION
SELECT 'A03' UNION
SELECT 'A04'


SELECT A.Id
FROM (SELECT ID,COUNT(1) AS num FROM table1 GROUP BY Id) AS A,(SELECT ID,COUNT(1) AS num FROM table2 GROUP BY Id) AS B
WHERE A.Id = B.Id AND A.num = B.num

Id
A01
A04

[Mr_Nice]

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

[风一样的大叔]

[Quote=引用 3 楼 的回复:]

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
)
INSER……
[/Quote]这个不是从table3中取的数据，

[风一样的大叔]

我就是在输入数据前判断下，代码量不要太大

[孤独加百列]

[Quote=引用 5 楼 的回复:]

引用 3 楼 的回复:

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……
[/Quote]

SELECT A.Id
FROM (SELECT ID,COUNT(1) AS num FROM table1 GROUP BY Id) AS A,(SELECT ID,COUNT(1) AS num FROM table2 GROUP BY Id) AS B,table3 AS C
WHERE A.Id = B.Id AND A.num = B.num AND B.Id = C.id

但是貌似不用table3，可能出现table1出现，table3中没有的吗？

[风一样的大叔]

[Quote=引用 7 楼 的回复:]

引用 5 楼 的回复:

引用 3 楼 的回复:

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),
pas……
[/Quote]table3是后来依据table1和2加的表，用来对table1,2进行操作，没有前面两个表中的数据