create table [dbo].[indexs](idxNum varchar (20),parentID varchar (20))
go
insert into [dbo].[indexs] values('1','0')
insert into [dbo].[indexs] values('1.1','1')
insert into [dbo].[indexs] values('1.1.1','1.1')
insert into [dbo].[indexs] values('1.1.2','1.1')
insert into [dbo].[indexs] values('1.2','1')
insert into [dbo].[indexs] values('1.2.1','1.2')
insert into [dbo].[indexs] values('1.2.2','1.2')
insert into [dbo].[indexs] values('2','0')
insert into [dbo].[indexs] values('2.1','2')
insert into [dbo].[indexs] values('2.2','2')
create table [dbo].[dutydis](dutyID int,idxNum varchar (20),dutyShould decimal(5,2))
go
insert into [dbo].[dutydis] values(1,'1',300)
insert into [dbo].[dutydis] values(2,'1.1',200)
insert into [dbo].[dutydis] values(3,'1.1.1',150)
insert into [dbo].[dutydis] values(4,'1.1.2',50)
insert into [dbo].[dutydis] values(5,'1.2',100)
insert into [dbo].[dutydis] values(6,'1.2.1',30)
insert into [dbo].[dutydis] values(7,'1.2.2',70)
insert into [dbo].[dutydis] values(8,'2',60)
insert into [dbo].[dutydis] values(9,'2.1',20)
insert into [dbo].[dutydis] values(10,'2.2',40)
indexs表是树形结构,dutydis用来根据indexs表进行一些操作。请帮忙在dutydis表中找出所有的最底层子节点。谢谢。(不知道幽灵会不会来,偷笑一个)
go
insert into [dbo].[indexs] values('1','0')
insert into [dbo].[indexs] values('1.1','1')
insert into [dbo].[indexs] values('1.1.1','1.1')
insert into [dbo].[indexs] values('1.1.2','1.1')
insert into [dbo].[indexs] values('1.2','1')
insert into [dbo].[indexs] values('1.2.1','1.2')
insert into [dbo].[indexs] values('1.2.2','1.2')
insert into [dbo].[indexs] values('2','0')
insert into [dbo].[indexs] values('2.1','2')
insert into [dbo].[indexs] values('2.2','2')
create table [dbo].[dutydis](dutyID int,idxNum varchar (20),dutyShould decimal(5,2))
go
insert into [dbo].[dutydis] values(1,'1',300)
insert into [dbo].[dutydis] values(2,'1.1',200)
insert into [dbo].[dutydis] values(3,'1.1.1',150)
insert into [dbo].[dutydis] values(4,'1.1.2',50)
insert into [dbo].[dutydis] values(5,'1.2',100)
insert into [dbo].[dutydis] values(6,'1.2.1',30)
insert into [dbo].[dutydis] values(7,'1.2.2',70)
insert into [dbo].[dutydis] values(8,'2',60)
insert into [dbo].[dutydis] values(9,'2.1',20)
insert into [dbo].[dutydis] values(10,'2.2',40)
indexs表是树形结构,dutydis用来根据indexs表进行一些操作。请帮忙在dutydis表中找出所有的最底层子节点。谢谢。(不知道幽灵会不会来,偷笑一个)
go
insert into [dbo].[indexs] values('1','0')
insert into [dbo].[indexs] values('1.1','1')
insert into [dbo].[indexs] values('1.1.1','1.1')
insert into [dbo].[indexs] values('1.1.2','1.1')
insert into [dbo].[indexs] values('1.2','1')
insert into [dbo].[indexs] values('1.2.1','1.2')
insert into [dbo].[indexs] values('1.2.2','1.2')
insert into [dbo].[indexs] values('2','0')
insert into [dbo].[indexs] values('2.1','2')
insert into [dbo].[indexs] values('2.2','2')
create table [dbo].[dutydis](dutyID int,idxNum varchar (20),dutyShould decimal(5,2))
go
insert into [dbo].[dutydis] values(1,'1',300)
insert into [dbo].[dutydis] values(2,'1.1',200)
insert into [dbo].[dutydis] values(3,'1.1.1',150)
insert into [dbo].[dutydis] values(4,'1.1.2',50)
insert into [dbo].[dutydis] values(5,'1.2',100)
insert into [dbo].[dutydis] values(6,'1.2.1',30)
insert into [dbo].[dutydis] values(7,'1.2.2',70)
insert into [dbo].[dutydis] values(8,'2',60)
insert into [dbo].[dutydis] values(9,'2.1',20)
insert into [dbo].[dutydis] values(10,'2.2',40)
go
select dutyID,idxNum,dutyShould from [dbo].[dutydis] a
where not exists(select 1 from [dbo].[indexs] where parentID=a.idxNum)
drop table [dbo].[indexs]
drop table [dbo].[dutydis]
/*
dutyID idxNum dutyShould
----------- -------------------- ---------------------------------------
3 1.1.1 150.00
4 1.1.2 50.00
6 1.2.1 30.00
7 1.2.2 70.00
9 2.1 20.00
10 2.2 40.00(6 row(s) affected)
*/
http://community.csdn.net/Expert/topic/5703/5703142.xml?temp=.6090052