如果只有两级---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(1),[up_id] int) insert [tb] select 1,'a',0 union all select 2,'b',0 union all select 3,'c',1 union all select 4,'d',1 union all select 5,'e',2
---查询--- select *, number=(select count(1) from tb where up_id=t.id) from tb t where up_id=0---结果--- id name up_id number ----------- ---- ----------- ----------- 1 a 0 2 2 b 0 1(所影响的行数为 2 行)
if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(1),[up_id] int) insert [tb] select 1,'a',0 union all select 2,'b',0 union all select 3,'c',1 union all select 4,'d',1 union all select 5,'e',2
select * from ( select * ,number = (select count(1) from tb where up_id = a.id) from tb a )t where number > 0 /* id name up_id number ----------- ---- ----------- ----------- 1 a 0 2 2 b 0 1(2 row(s) affected) */
declare @tb table (id int, name nvarchar(10),up_id int) insert into @tb select 1,'a',0 union all select 2,'b',0 union all select 3,'c',1 union all select 4,'d',1 union all select 5,'e',2 select a.id,a.name,a.up_id,COUNT(*) 次数 from @tb a join @tb b on a.id=b.up_id group by a.id,a.name,a.up_idid name up_id 次数 ----------- ---------- ----------- ----------- 1 a 0 2 2 b 0 1(2 行受影响)
with wang as (select * from tb where up_id=0 union all select tb.* from tb ,wang where tb.up_id=wang.id) select tb.*,count(*) from tb,wang where tb.id=wang.up_id group by tb.id,tb.name,tb.up_idid name up_id ----------- ---- ----- ----------- 1 a 0 2 2 b 0 1(2 行受影响)
declare @tb table (id int, name nvarchar(10),up_id int) insert into @tb select 1,'a',0 union all select 2,'b',0 union all select 3,'c',1 union all select 4,'d',1 union all select 5,'e',2 select id,name,up_id,(select count(1) from @tb where up_id=a.id) as number from @tb a where up_id=0
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1),[up_id] int)
insert [tb]
select 1,'a',0 union all
select 2,'b',0 union all
select 3,'c',1 union all
select 4,'d',1 union all
select 5,'e',2
---查询---
select
*,
number=(select count(1) from tb where up_id=t.id)
from tb t
where up_id=0---结果---
id name up_id number
----------- ---- ----------- -----------
1 a 0 2
2 b 0 1(所影响的行数为 2 行)
go
create table [tb]([id] int,[name] varchar(1),[up_id] int)
insert [tb]
select 1,'a',0 union all
select 2,'b',0 union all
select 3,'c',1 union all
select 4,'d',1 union all
select 5,'e',2
select * from
(
select *
,number = (select count(1) from tb where up_id = a.id)
from tb a
)t where number > 0
/*
id name up_id number
----------- ---- ----------- -----------
1 a 0 2
2 b 0 1(2 row(s) affected)
*/
declare @tb table (id int, name nvarchar(10),up_id int)
insert into @tb select 1,'a',0
union all select 2,'b',0
union all select 3,'c',1
union all select 4,'d',1
union all select 5,'e',2
select a.id,a.name,a.up_id,COUNT(*) 次数 from @tb a join @tb b on a.id=b.up_id
group by
a.id,a.name,a.up_idid name up_id 次数
----------- ---------- ----------- -----------
1 a 0 2
2 b 0 1(2 行受影响)
wang as (select * from tb where up_id=0 union all
select tb.* from tb ,wang where tb.up_id=wang.id)
select tb.*,count(*) from tb,wang where tb.id=wang.up_id
group by tb.id,tb.name,tb.up_idid name up_id
----------- ---- ----- -----------
1 a 0 2
2 b 0 1(2 行受影响)
insert into @tb select 1,'a',0
union all select 2,'b',0
union all select 3,'c',1
union all select 4,'d',1
union all select 5,'e',2
select id,name,up_id,(select count(1) from @tb where up_id=a.id) as number
from @tb a where up_id=0