实例：通过省市联动找到对应地方的酒店

                         中国

                        /        \

                   广东        广西

                  /    \

               江门  中山

              /    \

           蓬江

          /

      xxx酒店

 1 CREATE TABLE [dbo].[Adress](
 2     [placeId] [int] IDENTITY(1,1) NOT NULL,
 3     [adress] [varchar](50) NOT NULL,
 4     [flag] [int] NULL,
 5  CONSTRAINT [PK_Adress] PRIMARY KEY CLUSTERED 
 6 (
 7     [placeId] ASC
 8 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 9 ) ON [PRIMARY]
10 GO
11 SET ANSI_PADDING OFF
12 GO
13 SET IDENTITY_INSERT [dbo].[Adress] ON
14 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (2, N'中国', NULL)
15 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (3, N'日本', NULL)
16 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (4, N'美国', NULL)
17 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (5, N'英国', NULL)
18 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (6, N'广东', 2)
19 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (7, N'广西', 2)
20 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (8, N'湖北', 2)
21 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (9, N'广州', 6)
22 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (10, N'江门', 6)
23 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (11, N'中山', 6)
24 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (12, N'黄冈', 8)
25 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (13, N'东区', 11)
26 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (14, N'西区', 11)
27 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (15, N'博爱7路', 13)
28 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (16, N'中职', 15)
29 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (17, N'医药', 15)
30 INSERT [dbo].[Adress] ([placeId], [adress], [flag]) VALUES (18, N'天河', 9)
31 SET IDENTITY_INSERT [dbo].[Adress] OFF
32 /****** Object:  Table [dbo].[Hotel]    Script Date: 06/28/2015 19:08:08 ******/
33 SET ANSI_NULLS ON
34 GO
35 SET QUOTED_IDENTIFIER ON
36 GO
37 SET ANSI_PADDING ON
38 GO
39 CREATE TABLE [dbo].[Hotel](
40     [hotelId] [int] IDENTITY(1,1) NOT NULL,
41     [adressId] [int] NOT NULL,
42     [hotelName] [varchar](50) NOT NULL,
43  CONSTRAINT [PK_Hotel] PRIMARY KEY CLUSTERED 
44 (
45     [hotelId] ASC
46 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
47 ) ON [PRIMARY]
48 GO
49 SET ANSI_PADDING OFF
50 GO
51 SET IDENTITY_INSERT [dbo].[Hotel] ON
52 INSERT [dbo].[Hotel] ([hotelId], [adressId], [hotelName]) VALUES (1, 16, N'实训酒店')
53 INSERT [dbo].[Hotel] ([hotelId], [adressId], [hotelName]) VALUES (2, 17, N'酒店')
54 INSERT [dbo].[Hotel] ([hotelId], [adressId], [hotelName]) VALUES (3, 16, N'实训酒店2')
55 INSERT [dbo].[Hotel] ([hotelId], [adressId], [hotelName]) VALUES (4, 16, N'实训酒店3')
56 INSERT [dbo].[Hotel] ([hotelId], [adressId], [hotelName]) VALUES (5, 15, N'xxhitel')
57 SET IDENTITY_INSERT [dbo].[Hotel] OFF
数据库样例
 1 Create Function [dbo].[GetParentAdress](@ID int)
 2 Returns @Tree Table (ID Int, Name Varchar(50),fatherID Int)
 3 As
 4 Begin
 5 Insert @Tree Select placeId,adress,flag From Adress Where placeId = @ID
 6 While @@Rowcount > 0
 7 Insert @Tree Select Adress.placeId,Adress.adress,Adress.flag From Adress Adress Inner Join @Tree B On Adress.placeId = B.fatherID And Adress.placeId Not In (Select ID From @Tree)
 8 Return
 9 End
10 GO

遍历父节点的函数

 

 

 1 Create Function [dbo].[GetChildrenAdress](@ID int)
 2 Returns @Tree Table (ID Int,Name Varchar(50), fatherID Int)
 3 As
 4 Begin
 5 Insert @Tree Select placeId,adress,flag From Adress Where flag = @ID
 6 While @@Rowcount > 0
 7 Insert @Tree Select Adress.placeId,Adress.adress,Adress.flag From Adress Adress Inner Join @Tree B On Adress.flag = B.ID And Adress.placeId Not In (Select ID From @Tree)
 8 Return
 9 End
10 GO

遍历子节点函数

 

把找到的数据存储在B 树表里面

可以select 出B

select * from Hotel where adressId in(select ID from GetChildrenAdress(11))

找到中山以下的酒店名字