create table test
(
站编号 varchar(20),
站名 varchar(20),
流域 varchar(20),
水系 varchar(20),
河流 varchar(20)
)goinsert into test values('10000011','安阳','海河流域(片)','淮河','淮河')
insert into test values('10000012','八里营','松辽流域(片)','洪河','洪河')
insert into test values('10000013','宝泉水库','淮河流域(片)','洪泽湖','奎河')
insert into test values('10000014','合河(共)','黄河流域(片)','淠河','新濉河')
insert into test values('10000015','和平桥','长江流域(片)','颍河','东五龙河')
insert into test values('10000016','横水','珠江流域(片)','涡河','新沭河')
insert into test values('10000017','黄土岗','东南诸河','江河','浉河')
insert into test values('10000018','珠江','东南诸河','江河','浉河')go
--以上为建立测试环境
create table #temp
(
T_nodeID int identity(1,1),
T_parentID int,
T_nodeinfo varchar(20),
站编号 varchar(20)
)godeclare @lastid int,@lastid1 intinsert into #temp select 0,流域,'FOLDER' from test group by 流域select @lastid=scope_identity()insert into #temp
select min(b.T_nodeID),a.水系,'FOLDER'
from test a join #temp b on a.流域=b.T_nodeinfo
group by a.水系select @lastid1=scope_identity()insert into #temp
select min(b.T_nodeID),a.河流,'FOLDER'
from test a join #temp b on a.水系=b.T_nodeinfo
where b.T_nodeID>@lastid
group by a.河流insert into #temp
select b.T_nodeID,a.站名,a.站编号
from test a join #temp b on a.河流=b.T_nodeinfo
where b.T_nodeID>@lastid1goselect * from #temp--删除测试环境
drop table #temp
drop table test
from(
select T_nodeID=(select count(distinct 流域) from basic where 站编号<=a.站编号)
,T_parentID=0,T_nodeinfo=流域,站编号='FOLDER'
from(select 站编号=min(站编号),流域 from basic group by 流域)aunion all
select T_nodeID=(select count(distinct 水系) from basic where 水系<=a.水系)
+(select count(distinct 流域) from basic)
,T_parentID=(
select count(distinct 流域) from basic
where 站编号<=(select min(站编号) from basic where 流域=min(a.流域)))
,T_nodeinfo=水系,站编号='FOLDER'
from basic a
group by 水系union all
select T_nodeID=(select count(distinct 河流) from basic where 河流<=a.河流)
+(select count(distinct 流域) from basic)
+(select count(distinct 水系) from basic)
,T_parentID=(
select count(distinct 水系) from basic
where 站编号<=(select min(站编号) from basic where 水系=min(a.水系)))
+(select count(distinct 流域) from basic)
,T_nodeinfo=河流,站编号='FOLDER'
from basic a
group by 河流union all
select T_nodeID=(select count(distinct 站名) from basic where 站名<=a.站名)
+(select count(distinct 流域) from basic)
+(select count(distinct 水系) from basic)
+(select count(distinct 河流) from basic)
,T_parentID=(
select count(distinct 河流) from basic
where 站编号<=(select min(站编号) from basic where 河流=a.河流))
+(select count(distinct 流域) from basic)
+(select count(distinct 河流) from basic)
,T_nodeinfo=站名,站编号
from basic a
)a order by T_nodeID
as
select top 100 percent *
from(
select T_nodeID=(select count(distinct 流域) from basic where 站编号<=a.站编号)
,T_parentID=0,T_nodeinfo=流域,站编号='FOLDER'
from(select 站编号=min(站编号),流域 from basic group by 流域)aunion all
select T_nodeID=(select count(distinct 水系) from basic where 水系<=a.水系)
+(select count(distinct 流域) from basic)
,T_parentID=(
select count(distinct 流域) from basic
where 站编号<=(select min(站编号) from basic where 流域=min(a.流域)))
,T_nodeinfo=水系,站编号='FOLDER'
from basic a
group by 水系union all
select T_nodeID=(select count(distinct 河流) from basic where 河流<=a.河流)
+(select count(distinct 流域) from basic)
+(select count(distinct 水系) from basic)
,T_parentID=(
select count(distinct 水系) from basic
where 站编号<=(select min(站编号) from basic where 水系=min(a.水系)))
+(select count(distinct 流域) from basic)
,T_nodeinfo=河流,站编号='FOLDER'
from basic a
group by 河流union all
select T_nodeID=(select count(distinct 站名) from basic where 站名<=a.站名)
+(select count(distinct 流域) from basic)
+(select count(distinct 水系) from basic)
+(select count(distinct 河流) from basic)
,T_parentID=(
select count(distinct 河流) from basic
where 站编号<=(select min(站编号) from basic where 河流=a.河流))
+(select count(distinct 流域) from basic)
+(select count(distinct 河流) from basic)
,T_nodeinfo=站名,站编号
from basic a
)a order by T_nodeID
create table basic(站编号 char(8),站名 varchar(10),流域 varchar(20),水系 varchar(10),河流 varchar(10))
insert basic select '10000011','安阳' ,'海河流域(片)','淮河' ,'淮河'
union all select '10000012','八里营' ,'松辽流域(片)','洪河' ,'洪河'
union all select '10000013','宝泉水库','淮河流域(片)','洪泽湖','奎河'
union all select '10000014','合河(共)','黄河流域(片)','淠河' ,'新濉河'
union all select '10000015','和平桥' ,'长江流域(片)','颍河' ,'东五龙河'
union all select '10000016','横水' ,'珠江流域(片)','涡河' ,'新沭河'
union all select '10000017','黄土岗' ,'东南诸河' ,'江河' ,'浉河'
union all select '10000018','珠江' ,'东南诸河' ,'江河' ,'浉河'
go--查询
select *
from(
select T_nodeID=(select count(distinct 流域) from basic where 站编号<=a.站编号)
,T_parentID=0,T_nodeinfo=流域,站编号='FOLDER'
from(select 站编号=min(站编号),流域 from basic group by 流域)aunion all
select T_nodeID=(select count(distinct 水系) from basic where 水系<=a.水系)
+(select count(distinct 流域) from basic)
,T_parentID=(
select count(distinct 流域) from basic
where 站编号<=(select min(站编号) from basic where 流域=min(a.流域)))
,T_nodeinfo=水系,站编号='FOLDER'
from basic a
group by 水系union all
select T_nodeID=(select count(distinct 河流) from basic where 河流<=a.河流)
+(select count(distinct 流域) from basic)
+(select count(distinct 水系) from basic)
,T_parentID=(
select count(distinct 水系) from basic
where 站编号<=(select min(站编号) from basic where 水系=min(a.水系)))
+(select count(distinct 流域) from basic)
,T_nodeinfo=河流,站编号='FOLDER'
from basic a
group by 河流union all
select T_nodeID=(select count(distinct 站名) from basic where 站名<=a.站名)
+(select count(distinct 流域) from basic)
+(select count(distinct 水系) from basic)
+(select count(distinct 河流) from basic)
,T_parentID=(
select count(distinct 河流) from basic
where 站编号<=(select min(站编号) from basic where 河流=a.河流))
+(select count(distinct 流域) from basic)
+(select count(distinct 河流) from basic)
,T_nodeinfo=站名,站编号
from basic a
)a order by T_nodeID
go--删除测试
drop table basic/*--测试结果T_nodeID T_parentID T_nodeinfo 站编号
----------- ----------- -------------------- --------
1 0 海河流域(片) FOLDER
2 0 松辽流域(片) FOLDER
3 0 淮河流域(片) FOLDER
4 0 黄河流域(片) FOLDER
5 0 长江流域(片) FOLDER
6 0 珠江流域(片) FOLDER
7 0 东南诸河 FOLDER
8 2 洪河 FOLDER
9 3 洪泽湖 FOLDER
10 1 淮河 FOLDER
11 7 江河 FOLDER
12 4 淠河 FOLDER
13 6 涡河 FOLDER
14 5 颍河 FOLDER
15 12 东五龙河 FOLDER
16 9 洪河 FOLDER
17 8 淮河 FOLDER
18 10 奎河 FOLDER
19 14 浉河 FOLDER
20 13 新沭河 FOLDER
21 11 新濉河 FOLDER
22 15 安阳 10000011
23 16 八里营 10000012
24 17 宝泉水库 10000013
25 18 合河(共) 10000014
26 19 和平桥 10000015
27 20 横水 10000016
28 21 黄土岗 10000017
29 21 珠江 10000018（所影响的行数为 29 行）
--*/
如果数据如下:(插入相同的流域,和水系)就会生成如下的结果:
站编号 站名 流域 水系 河流
10000011 安阳 海河流域(片) 淮河 淮河
10000012 八里营 海河流域(片) 淮河 洪河
10000013 宝泉水库 淮河流域(片) 洪泽湖 奎河
10000014 合河(共) 黄河流域(片) 淠河 新濉河
10000015 和平桥 长江流域(片) 颍河 东五龙河
10000016 横水 珠江流域(片) 涡河 新沭河
10000017 黄土岗 东南诸河 江河 浉河
10000018 珠江 东南诸河 江河 浉河 T_nodeID T_parentID T_nodeinfo 站编号
1 0 海河流域(片) FOLDER
2 0 淮河流域(片) FOLDER
3 0 黄河流域(片) FOLDER
4 0 长江流域(片) FOLDER
5 0 珠江流域(片) FOLDER
6 0 东南诸河 FOLDER 7 2 洪泽湖 FOLDER
8 1 淮河 FOLDER
9 6 江河 FOLDER
10 3 淠河 FOLDER
11 5 涡河 FOLDER
12 4 颍河 FOLDER 13 10 (12) 东五龙河 FOLDER '它的T_parentID 应该为12)它的水 系颍河ID 为12
14 7(8) 洪河 FOLDER
15 7(8) 淮河 FOLDER
16 8 奎河 FOLDER
17 12 浉河 FOLDER
18 11 新沭河 FOLDER
19 9 新濉河 FOLDER
20 14 安阳 10000011
21 15 八里营 10000012
22 16 宝泉水库 10000013
23 17 合河(共) 10000014
24 18 和平桥 10000015
25 19 横水 10000016
26 20 黄土岗 10000017
27 20 珠江 10000018河流的T_parentID 均有错误..谢谢
好难!!!
如果数据如下:(插入相同的流域,和水系)就会生成如下的结果:
站编号 站名 流域 水系 河流
10000011 安阳 海河流域(片) 淮河 淮河
10000012 八里营 海河流域(片) 淮河 洪河
10000013 宝泉水库 淮河流域(片) 洪泽湖 奎河
10000014 合河(共) 黄河流域(片) 淠河 新濉河
10000015 和平桥 长江流域(片) 颍河 东五龙河
10000016 横水 珠江流域(片) 涡河 新沭河
10000017 黄土岗 东南诸河 江河 浉河
10000018 珠江 东南诸河 江河 浉河 T_nodeID T_parentID T_nodeinfo 站编号
1 0 海河流域(片) FOLDER
2 0 淮河流域(片) FOLDER
3 0 黄河流域(片) FOLDER
4 0 长江流域(片) FOLDER
5 0 珠江流域(片) FOLDER
6 0 东南诸河 FOLDER 7 2 洪泽湖 FOLDER
8 1 淮河 FOLDER
9 6 江河 FOLDER
10 3 淠河 FOLDER
11 5 涡河 FOLDER
12 4 颍河 FOLDER 13 10 (12) 东五龙河 FOLDER '它的T_parentID 应该为12)它的水 系颍河ID 为12
14 7(8) 洪河 FOLDER
15 7(8) 淮河 FOLDER
16 8 奎河 FOLDER
17 12 浉河 FOLDER
18 11 新沭河 FOLDER
19 9 新濉河 FOLDER
20 14 安阳 10000011
21 15 八里营 10000012
22 16 宝泉水库 10000013
23 17 合河(共) 10000014
24 18 和平桥 10000015
25 19 横水 10000016
26 20 黄土岗 10000017
27 20 珠江 10000018河流的T_parentID 均有错误..谢谢
好难!!!
(
select distinct
lno+1 as T_nodeID,
0 as T_parentID,
流域 T_nodeinfo,
'FOLDER' as 站编号
from
(select 站编号,站名,流域,水系,河流,
(select count(distinct t1.流域) from test t1 where t1.流域<tt.流域) as lno
from test tt) ttt
union all
select distinct
(select count(distinct 流域) from test)+sno+1 as T_nodeID,
lno+1 as T_parentID,
水系 T_nodeinfo,
'FOLDER' as 站编号
from
(select 站编号,站名,流域,水系,河流,
(select count(distinct t1.流域) from test t1 where t1.流域<tt.流域) as lno,
(select count(distinct t1.水系) from test t1 where t1.水系<tt.水系) as sno
from test tt) ttt
union all
select distinct
(select count(distinct 流域) from test)+(select count(distinct 水系) from test)+hno+1 as T_nodeID,
(select count(distinct 流域) from test)+sno+1 as T_parentID,
河流 T_nodeinfo,
'FOLDER' as 站编号
from
(select 站编号,站名,流域,水系,河流,
(select count(distinct t1.水系) from test t1 where t1.水系<tt.水系) as sno,
(select count(distinct t1.河流) from test t1 where t1.河流<tt.河流) as hno
from test tt) ttt
union all
select distinct
(select count(distinct 流域) from test)+(select count(distinct 水系) from test)+(select count(distinct 河流) from test)+zno+1 as T_nodeID,
(select count(distinct 流域) from test)+(select count(distinct 水系) from test)+hno+1 as T_parentID,
站名 T_nodeinfo,
站编号
from
(select 站编号,站名,流域,水系,河流,
(select count(distinct t1.河流) from test t1 where t1.河流<tt.河流) as hno,
(select count(distinct t1.站编号) from test t1 where t1.站编号<tt.站编号) as zno
from test tt) ttt
) tab order by T_nodeID