求一SQL语句，递归调用的

[u9085]

-- ----------------------------
-- Table structure for CompanyCg_copy
-- ----------------------------
DROP TABLE [dbo].[CompanyCg_copy]
GO
CREATE TABLE [dbo].[CompanyCg_copy] (
[ID] int NOT NULL IDENTITY(1,1) ,
[companyno] varchar(20) NULL ,
[companyname] varchar(50) NULL ,
[cgcompanyno] varchar(20) NULL ,
[cgcompanyname] varchar(50) NULL ,
[cgje] varchar(50) NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[CompanyCg_copy]', RESEED, 5)
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'CompanyCg_copy', 
'COLUMN', N'ID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'CompanyCg_copy'
, @level2type = 'COLUMN', @level2name = N'ID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'CompanyCg_copy'
, @level2type = 'COLUMN', @level2name = N'ID'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'CompanyCg_copy', 
'COLUMN', N'companyno')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'客户编号'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'CompanyCg_copy'
, @level2type = 'COLUMN', @level2name = N'companyno'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'客户编号'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'CompanyCg_copy'
, @level2type = 'COLUMN', @level2name = N'companyno'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'CompanyCg_copy', 
'COLUMN', N'companyname')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'客户名称'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'CompanyCg_copy'
, @level2type = 'COLUMN', @level2name = N'companyname'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'客户名称'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'CompanyCg_copy'
, @level2type = 'COLUMN', @level2name = N'companyname'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'CompanyCg_copy', 
'COLUMN', N'cgcompanyno')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'参股机构编号'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'CompanyCg_copy'
, @level2type = 'COLUMN', @level2name = N'cgcompanyno'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'参股机构编号'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'CompanyCg_copy'
, @level2type = 'COLUMN', @level2name = N'cgcompanyno'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'CompanyCg_copy', 
'COLUMN', N'cgcompanyname')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'参股机构名称'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'CompanyCg_copy'
, @level2type = 'COLUMN', @level2name = N'cgcompanyname'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'参股机构名称'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'CompanyCg_copy'
, @level2type = 'COLUMN', @level2name = N'cgcompanyname'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'CompanyCg_copy', 
'COLUMN', N'cgje')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'参股金额'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'CompanyCg_copy'
, @level2type = 'COLUMN', @level2name = N'cgje'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'参股金额'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'CompanyCg_copy'
, @level2type = 'COLUMN', @level2name = N'cgje'
GO

-- ----------------------------
-- Records of CompanyCg_copy
-- ----------------------------
SET IDENTITY_INSERT [dbo].[CompanyCg_copy] ON
GO
INSERT INTO [dbo].[CompanyCg_copy] ([ID], [companyno], [companyname], [cgcompanyno], [cgcompanyname], [cgje]) VALUES (N'1', N'A24255737', N'天水市麦积铝门窗厂', N'A24239032', N'靖远塑料薄膜厂', N'500')
GO
GO
INSERT INTO [dbo].[CompanyCg_copy] ([ID], [companyno], [companyname], [cgcompanyno], [cgcompanyname], [cgje]) VALUES (N'2', N'A24239032', N'靖远塑料薄膜厂', N'A24152391', N'静宁县有限责任公司', N'500')
GO
GO
INSERT INTO [dbo].[CompanyCg_copy] ([ID], [companyno], [companyname], [cgcompanyno], [cgcompanyname], [cgje]) VALUES (N'3', N'A2415993X', N'临夏州清真食品有限责任公司', null, null, null)
GO
GO
INSERT INTO [dbo].[CompanyCg_copy] ([ID], [companyno], [companyname], [cgcompanyno], [cgcompanyname], [cgje]) VALUES (N'4', N'A24152391', N'静宁县有限责任公司', N'A24255737', N'天水市麦积铝门窗厂', N'300')
GO
GO
INSERT INTO [dbo].[CompanyCg_copy] ([ID], [companyno], [companyname], [cgcompanyno], [cgcompanyname], [cgje]) VALUES (N'5', N'A24255737', N'天水市麦积铝门窗厂', N'A24152391', N'静宁县有限责任公司', N'500')
GO
GO
SET IDENTITY_INSERT [dbo].[CompanyCg_copy] OFF
GO

-- ----------------------------
-- Indexes structure for table CompanyCg_copy
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table CompanyCg_copy
-- ----------------------------
ALTER TABLE [dbo].[CompanyCg_copy] ADD PRIMARY KEY ([ID])
GO

求一条SQL语句，选中某一公司，可以查找出与之有关联的参股公司，因为有环路，所以递归调用不能算出结果，请大侠给支个招，谢谢！

with cte(companyno,companyname,cgcompanyno,cgcompanyname,cgje) 
as 
(--父项 
select companyno,companyname,cgcompanyno,cgcompanyname,cgje from companycg where companyno = 'A24255737' 
union all 
--递归结果集中的下级 
select t.companyno,t.companyname,t.cgcompanyno,t.cgcompanyname,t.cgje from companycg as t 
inner join cte as c on t.companyno = c.cgcompanyno 
) 
select companyno,companyname,cgcompanyno,cgcompanyname,cgje from cte where cgcompanyno is not null

[zjcxc]

with cte(companyno,companyname,cgcompanyno,cgcompanyname,cgje, ids) 
as 
(--父项 
select companyno,companyname,cgcompanyno,cgcompanyname,cgje 
	,convert(varchar(max),','+rtrim(id)+',') as ids
from [CompanyCg_copy] where companyno = 'A24255737' 
union all 
--递归结果集中的下级 
select t.companyno,t.companyname,t.cgcompanyno,t.cgcompanyname,t.cgje 
	,','+rtrim(t.id)+c.ids
from [CompanyCg_copy] as t 
inner join cte as c on t.companyno = c.cgcompanyno 
where charindex(','+rtrim(id)+',', c.ids)=0
) 
select companyno,companyname,cgcompanyno,cgcompanyname,cgje from cte where cgcompanyno is not null

[caoyang0299]

没分啦，借用下楼主的帖子，cte第一次用，下面的代码有啥问题，为啥查不出来后续的子节点呢，谢谢。 （顺便问下如何才能赚积分啊） WITH cte AS ( select ParentBasetId,ChildBasetId,ChildCount from MAT_MaterialRelation where ParentBasetId='db2329b1-1d33-4d2d-b902-de57d3c3ab60' union all select a.ParentBasetId,a.ChildBasetId,a.ChildCount from cte a inner join MAT_MaterialRelation b on a.ParentBasetId=b.ChildBasetId ) select * from cte

[caoyang0299]

解决，写反啦

[u9085]

引用 1 楼 zjcxc 的回复:

with cte(companyno,companyname,cgcompanyno,cgcompanyname,cgje, ids) 
as 
(--父项 
select companyno,companyname,cgcompanyno,cgcompanyname,cgje 
	,convert(varchar(max),','+rtrim(id)+',') as ids
from [CompanyCg_copy] where companyno = 'A24255737' 
union all 
--递归结果集中的下级 
select t.companyno,t.companyname,t.cgcompanyno,t.cgcompanyname,t.cgje 
	,','+rtrim(t.id)+c.ids
from [CompanyCg_copy] as t 
inner join cte as c on t.companyno = c.cgcompanyno 
where charindex(','+rtrim(id)+',', c.ids)=0
) 
select companyno,companyname,cgcompanyno,cgcompanyname,cgje from cte where cgcompanyno is not null

非常感谢，就是还有一点，最后两条记录是相互参股的，计算的结果只有一条记录，没有算出第二条记录，能不能再给改一下，谢谢呀！

[u9085]

完全满足要求