原始库:
ID KeyWord C
1 abcdefg 0
2 123 1
3 1233 2
4 123123 1
5 123123 0
6 asdfa 5
7 adf 5Select 后的结果
ID KeyWord C D
1 abcdefg 0 2
2 123 1 1
3 1233 2 0
4 123123 1 0
5 123123 0 2
6 asdfa 5 0
7 adf 5 0C列为隶属于上一级的ID
D列为本级有多少个下属
求生成结果表的Select写法select * from Tmp
select count(*) from Tmp where C...
?
总感觉好像差点什么...
不知道能不能输出成这个样子
ID KeyWord C
1 abcdefg 0
2 123 1
3 1233 2
4 123123 1
5 123123 0
6 asdfa 5
7 adf 5Select 后的结果
ID KeyWord C D
1 abcdefg 0 2
2 123 1 1
3 1233 2 0
4 123123 1 0
5 123123 0 2
6 asdfa 5 0
7 adf 5 0C列为隶属于上一级的ID
D列为本级有多少个下属
求生成结果表的Select写法select * from Tmp
select count(*) from Tmp where C...
?
总感觉好像差点什么...
不知道能不能输出成这个样子
select ID, KeyWord, C,D=(select count(*)from TB where C=T.ID) from TB T
if object_id('[tt]') is not null drop table [tt]
go
create table [tt] (ID int,KeyWord varchar(7),C int)
insert into [tt]
select 1,'abcdefg',0 union all
select 2,'123',1 union all
select 3,'1233',2 union all
select 4,'123123',1 union all
select 5,'123123',0 union all
select 6,'asdfa',5 union all
select 7,'adf',5select a.*,d=(select count(*) from tt where a.id=c) from [tt] a
ID KeyWord C d
----------- ------- ----------- -----------
1 abcdefg 0 2
2 123 1 1
3 1233 2 0
4 123123 1 0
5 123123 0 2
6 asdfa 5 0
7 adf 5 0(7 行受影响)
--环境
create table tb
(
id int,
KeyWord varchar(10),
c int
)insert into tb select 1, 'abcdefg', 0
insert into tb select 2, '123', 1
insert into tb select 3, '1233', 2
insert into tb select 4, '123123', 1
insert into tb select 5, '123123', 0
insert into tb select 6, 'asdfa', 5
insert into tb select 7, 'adf', 5--查询
select *,d = (select count(1) from tb where c = a.id)
from tb a--结果
/*
1 abcdefg 0 2
2 123 1 1
3 1233 2 0
4 123123 1 0
5 123123 0 2
6 asdfa 5 0
7 adf 5 0
*/--删除环境
drop table tb
直接下属-按上面方法写
多重-所有下属(sql2005)CREATE TABLE #tb
(
[ID] int,
[KeyWord] varchar(20),
[C] int
)
INSERT INTO #tb
SELECT 1 ,'abcdefg', 0 UNION ALL
SELECT 2 ,'123', 1 UNION ALL
SELECT 3 ,'1233', 2 UNION ALL
SELECT 4 ,'123123', 1 UNION ALL
SELECT 5 ,'123123', 0 UNION ALL
SELECT 6 ,'asdfa', 5 UNION ALL
SELECT 7 ,'adf', 5WITH cte AS
(
SELECT * FROM #tb
UNION ALL
SELECT b.* FROM cte a JOIN #tb b ON b.id = a.c
)SELECT a.*,b.D AS 所有下属 FROM
#tb a left JOIN
(
SELECT c,count(*) D FROM cte GROUP BY c
) b ON a.ID = b.c;ID KeyWord C 所有下属
----------- -------------------- ----------- -----------
1 abcdefg 0 3
2 123 1 1
3 1233 2 NULL
4 123123 1 NULL
5 123123 0 2
6 asdfa 5 NULL
7 adf 5 NULL(7 行受影响)
if object_id('[tt]') is not null drop table [tt]
go
create table [tt] (ID int,KeyWord varchar(7),C int)
insert into [tt]
select 1,'abcdefg',0 union all
select 2,'123',1 union all
select 3,'1233',2 union all
select 4,'123123',1 union all
select 5,'123123',0 union all
select 6,'asdfa',5 union all
select 7,'adf',5select a.*,d=(select count(*) from tt where a.id=c) from [tt] a
ID KeyWord C d
----------- ------- ----------- -----------
1 abcdefg 0 2
2 123 1 1
3 1233 2 0
4 123123 1 0
5 123123 0 2
6 asdfa 5 0
7 adf 5 0(7 行受影响)
Select 后的结果
ID KeyWord C D
1 abcdefg 0 2明显是直接下属