With AC as
(
--Querying the parent class information by ID
SELECT S_parentid from dbo. Mywhere s_id=109
UNION All
--Find information by parent class ID (here is the parent class ID of the parent class)
SELECT Ob.s_parentid from AC INNER JOIN dbo. MYob on OB. S_id=ac.s_parentid
)
--Recursive completion of reading the information in the collection
SELECT * FROM dbo. MY WHERE s_id in (SELECT * from AC) and s_soncount>0
--Table structure
/****** 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) is not NULL,
[S_syb] [INT] Not NULL,
[S_fullid] [varchar] () 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] () NULL,
[Org_name] [varchar] () NULL,
[Org_zjcode] [varchar] () NULL,
[Org_phone] [varchar] () NULL,
[Org_email] [varchar] () 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 = O N) 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 ' All bodies ', NUL L, NULL, NULL, NULL, or 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 ' internal machine ", 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 ', 0, 0, 1, 0, n ' 001001 ', N ' headquarters ', 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, +,, 2, 0, n ' 001003 ', N ' affiliate mechanism ', 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 ' one ', n ' one ', 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 (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 ' WB0 1 ', n ' WB01 ', n ' wb01 ', n ' one ', n ' one ', n ' one ', 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 (1, n ' 000000000200002 ', 4, 0, 1, 2, 0, n ' jm01 2 ', 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 ' 111 2232 ', 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 (1, n ' 000000000100007 ', 2, 0, 0, 1, 1, n ' 121 + ', 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 (1, n ' 000000000200004 ', 4, 0, 0, 2, 1, n ' 234 23423 ', 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 ' qqq QQ ', 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 (1, n ' 000000000200006 ', 4, 0, 0, 4, 1, n ' ads fasd45345 ', 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 ' 1112 2233 ', 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 ' 1112 adfasdfa2233 ', 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 ' 111 2adfasdfa2233sss ', 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 ' 111 2adfasdfa2233sss ', 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 ' 111w Qwqwq ', n ' 111 ', n ' 11111 ', n ' one ', n ' one ', n ' one ', 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 ' 2342 3 ', 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 (1, n ' 000000000200011 ', 4, 0, 0, 4, 0, n ' ADFS ADF ', 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 ' ads FASD ', 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 ' 1112 Adfasdfa2233sss ', 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
The SQL with AS statement uses the