有如下表:
table1
batchidId Id passtimes
101 A01 1
102 A01 2
103 A02 1
104 A02 2
105 A03 1
106 A04 1table2
batchidId Id state
101 A01 pass
102 A01 pass
103 A02 pass
106 A04 passtable3
id
A01
A02
A03
A04 我现在要在table3中取所有id曾经在 table1,table2中出现过的,并且在table1中出现的次数与table2中次数一样的数据
希望得到的数据位
id
A01
A04写在一条sql中,关键是where后面的不怎么会写,求高手
table1
batchidId Id passtimes
101 A01 1
102 A01 2
103 A02 1
104 A02 2
105 A03 1
106 A04 1table2
batchidId Id state
101 A01 pass
102 A01 pass
103 A02 pass
106 A04 passtable3
id
A01
A02
A03
A04 我现在要在table3中取所有id曾经在 table1,table2中出现过的,并且在table1中出现的次数与table2中次数一样的数据
希望得到的数据位
id
A01
A04写在一条sql中,关键是where后面的不怎么会写,求高手
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.numId
A01
A04
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',1if 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
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中没有的吗?