sql with as 语句使用

浏览数：30 / 时间：2015年06月12日

WITH ac AS
(
     --通过id查询父类信息
     SELECT s_ParentID FROM dbo.MYWHERE S_id=109
     UNION ALL
     --通过父类id找出信息（此处是通过父类id找出父类的父类）
     SELECT ob.s_ParentID FROM ac INNER JOIN dbo.MYob ON ob.S_id=ac.s_ParentID
)
--递归完成读取集合中信息
SELECT * FROM dbo.MY WHERE S_id IN(SELECT * FROM ac) AND s_SonCount>0

--表结构

/****** Object: Table [dbo].[MY] Script Date: 10/10/2014 16:07:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MY](
[S_id] [int] IDENTITY(100,1) NOT NULL,
[s_Syb] [int] NOT NULL,
[s_FullID] [varchar](50) NOT NULL,
[s_ParentID] [int] NOT NULL,
[s_SonALL] [int] NOT NULL,
[s_SonCount] [int] NOT NULL,
[orgType] [int] NOT NULL,
[IsUsePH] [bit] NOT NULL,
[Org_Code] [varchar](50) NULL,
[Org_Name] [varchar](50) NULL,
[Org_zjCode] [varchar](50) NULL,
[Org_Phone] [varchar](50) NULL,
[Org_Email] [varchar](50) NULL,
[Org_Address] [varchar](255) NULL,
[Org_Remark] [varchar](255) NULL,
[s_checkEdit] [timestamp] NOT NULL,
CONSTRAINT [PK_Table_ID] PRIMARY KEY NONCLUSTERED
(
[S_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[MY] ON
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (1, 0, N‘00000‘, 0, 2, 2, 0, 0, N‘0‘, N‘所有机构‘, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (2, 0, N‘0000000001‘, 1, 7, 7, 1, 0, N‘001‘, N‘内部机构‘, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (3, 0, N‘00000000010000600001‘, 110, 0, 0, 1, 0, N‘001001‘, N‘总公司‘, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (4, 0, N‘0000000002‘, 1, 14, 14, 2, 0, N‘001003‘, N‘加盟机构‘, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (108, 1, N‘00000000010000800004‘, 126, 0, 0, 1, 1, N‘122121‘, N‘122121‘, N‘122121‘, NULL, NULL, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (109, 1, N‘00000000010000800005‘, 126, 0, 0, 1, 1, N‘11‘, N‘11‘, N‘1231231‘, N‘123123123‘, N‘123123‘, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (110, 1, N‘000000000100006‘, 2, 0, 2, 1, 0, N‘ck01‘, N‘ck01‘, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (111, -1, N‘000000000200001‘, 4, 0, 0, 2, 1, N‘WB01‘, N‘WB01‘, N‘wb01‘, N‘11‘, N‘11‘, N‘11‘, N‘11‘)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (112, 1, N‘000000000200002‘, 4, 0, 1, 2, 0, N‘jm012‘, N‘jm012‘, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (113, -1, N‘000000000200003‘, 4, 0, 0, 2, 1, N‘1112232‘, N‘11232‘, N‘11232‘, N‘11232‘, N‘11232‘, N‘2323‘, N‘11232‘)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (114, -1, N‘000000000100007‘, 2, 0, 0, 1, 1, N‘12121‘, N‘1212121‘, N‘12121‘, N‘12121‘, N‘12121‘, N‘2121‘, N‘1212‘)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (115, -1, N‘000000000200004‘, 4, 0, 0, 2, 1, N‘23423423‘, N‘asdfasdfsadf‘, N‘asdfasdfsadf‘, N‘adfasdf‘, N‘adf‘, N‘asdfsadf‘, N‘af‘)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (126, 1, N‘000000000100008‘, 2, 0, 5, 1, 0, N‘qq‘, N‘qqq‘, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (127, -1, N‘000000000100009‘, 2, 0, 0, 1, 0, N‘qqqqq‘, N‘qqqqq‘, N‘qqqqq‘, NULL, NULL, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (132, -1, N‘000000000200006‘, 4, 0, 0, 4, 1, N‘adsfasd45345‘, N‘asdfadfsasdf2452345245‘, N‘asdfadfsasdf2452345245‘, N‘afdadf‘, N‘asdfasdf‘, N‘afdafds‘, N‘asdfadsf‘)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (133, 1, N‘000000000100010‘, 2, 0, 0, 1, 0, N‘11122233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (134, 1, N‘000000000100011‘, 2, 0, 0, 1, 0, N‘1112adfasdfa2233‘, N‘1112adfasdf2233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (135, -1, N‘000000000200007‘, 4, 0, 0, 2, 1, N‘1112adfasdfa2233sss‘, N‘1112assssdfasdf2233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (136, -1, N‘000000000200008‘, 4, 0, 0, 2, 1, N‘1112adfasdfa2233sss‘, N‘1112assssdfasdf2233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (138, 1, N‘000000000100012‘, 2, 0, 0, 1, 1, N‘111wqwqwq‘, N‘111‘, N‘11111‘, N‘11‘, N‘11‘, N‘11‘, N‘11‘)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (139, 1, N‘000000000200010‘, 4, 0, 0, 4, 0, N‘23423‘, N‘2342342‘, N‘2342342‘, N‘234‘, NULL, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (140, 1, N‘000000000200011‘, 4, 0, 0, 4, 0, N‘adfsadf‘, N‘adfasfd‘, N‘adfasfd‘, N‘sadfasdf‘, NULL, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (106, 1, N‘00000000010000800007‘, 126, 0, 0, 1, 1, N‘SSS‘, N‘SSS‘, N‘sss‘, NULL, N‘SS‘, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (107, 1, N‘00000000010000800006‘, 126, 0, 0, 1, 1, N‘11111‘, N‘111111‘, N‘111111‘, N‘123‘, N‘123‘, NULL, NULL)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (131, -1, N‘000000000200005‘, 4, 0, 0, 4, 1, N‘adsfasd‘, N‘asdfadfsasdf‘, N‘asdfadfsasdf‘, N‘afdadf‘, N‘asdfasdf‘, N‘afdafds‘, N‘asdfadsf‘)
INSERT [dbo].[MY] ([S_id], [s_Syb], [s_FullID], [s_ParentID], [s_SonALL], [s_SonCount], [orgType], [IsUsePH], [Org_Code], [Org_Name], [Org_zjCode], [Org_Phone], [Org_Email], [Org_Address], [Org_Remark]) VALUES (137, 1, N‘000000000200009‘, 4, 0, 0, 2, 1, N‘1112adfasdfa2233sss‘, N‘1112assssdfasdf2233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘, N‘11122233‘)
SET IDENTITY_INSERT [dbo].[MY] OFF
/****** Object: Default [DF_OrgBase_s_SonALL] Script Date: 10/10/2014 16:07:36 ******/
ALTER TABLE [dbo].[MY] ADD CONSTRAINT [DF_MY_s_SonALL] DEFAULT ((0)) FOR [s_SonALL]
GO