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

判断出现次数是否相同
有如下表：
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 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……

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

相关推荐