表A:
ID CODE NAME
1 001 aaa
2 002 bbb
3 003 ccc表B:
ID ParentID CODE NAME
1 1 01 aaa1
2 2 01 bbb1
3 2 02 bbb2
4 2 03 bbb3
5 3 01 ccc1
6 3 02 ccc2
想要以下结果,相同ParentID 在3件以上的,查询结果:ID ParentID CODE NAME CODE NAME COUNT
2 2 01 bbb1 002 bbb 3
3 2 02 bbb2 002 bbb 3
4 2 03 bbb3 002 bbb 3
ID CODE NAME
1 001 aaa
2 002 bbb
3 003 ccc表B:
ID ParentID CODE NAME
1 1 01 aaa1
2 2 01 bbb1
3 2 02 bbb2
4 2 03 bbb3
5 3 01 ccc1
6 3 02 ccc2
想要以下结果,相同ParentID 在3件以上的,查询结果:ID ParentID CODE NAME CODE NAME COUNT
2 2 01 bbb1 002 bbb 3
3 2 02 bbb2 002 bbb 3
4 2 03 bbb3 002 bbb 3
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[CODE] varchar(3),[NAME] varchar(3))
insert [tb]
select 1,'001','aaa' union all
select 2,'002','bbb' union all
select 3,'003','ccc'--> 测试数据:[tc]
if object_id('[tc]') is not null drop table [tc]
go
create table [tc]([ID] int,[ParentID] int,[CODE] varchar(2),[NAME] varchar(4))
insert [tc]
select 1,1,'01','aaa1' union all
select 2,2,'01','bbb1' union all
select 3,2,'02','bbb2' union all
select 4,2,'03','bbb3' union all
select 5,3,'01','ccc1' union all
select 6,3,'02','ccc2'
--------------开始查询--------------------------select c.ID,c.ParentID,c.CODE,c.NAME,b.CODE,b.NAME,d.[count] from [tc] c
join [tb] b on c.[ParentID]=b.[ID]
join(select[ParentID],count(*) as[count] from [tc] group by [ParentID] having count(*)>=3) d
on c.[ParentID]=d.[ParentID]
----------------结果----------------------------
/*
ID ParentID CODE NAME CODE NAME COUNT
----------- ----------- ---- ---- ---- ---- -----------
2 2 01 bbb1 002 bbb 3
3 2 02 bbb2 002 bbb 3
4 2 03 bbb3 002 bbb 3(3 行受影响)
*/