获取顾问的父ID列表 [英] To get the Parent id list of an advisor

问题描述

advisor_id  name       parent_id

1          Griff        0(assume)
2          christian    1
3          AArti        2
4          king_fisher  3
5          jason        4
6          smith        5
7          willis       6

< br $> b $ b



这是我的表结构，如果我要求树顾问_id = 7.你们知道树是6,5,4 ，3,2,1,0

这样，如果我要求advisor_id = 4那就必须3,2,1,0



我怎么能得到这个??。



i希望它清楚，

谢谢

This is my table structure, if i gonna ask tree for advisor_id =7 .you guys know the tree is 6,5,4,3,2,1,0
like this,if i gonna ask for advisor_id =4 it must 3,2,1,0

how can i get this ??.

i hope its clear,
Thanks

推荐答案

尝试：

Try:

SELECT CAST(create_by_user_id AS VARCHAR) + ',' FROM tbl_advisor_registration where advisor_id=@intro_id FOR XML PATH('')

删除了虚假空间 - OriginalGriff [/ edit]

[edit]Spurious space removed - OriginalGriff[/edit]

这里是：

Here it is :

declare @id int=7;
declare @tmp varchar(max)='';
declare  @t1 table(
advisor_id  int,name       varchar(50),parent_id int)
insert into @t1 values
(1,'Griff',0),(2,'christian',1),(3 ,'AArti',2),
(4,'king_fisher',3),(5,'jason',4),(6,'smith',5),(7,          'willis'       ,6);

with a(id , pid)
as
(select advisor_id,parent_id  from @t1 where advisor_id=@id
union all
select a.id, t.parent_id from a inner join @t1 t on a.pid = t.advisor_id
)
--select pid from a
select @tmp = @tmp + cast(pid as nvarchar(max)) + ', ' from a
select SUBSTRING(@tmp, 0, LEN(@tmp))

更改@ id值看看会发生什么。



祝你好运。

Change @id value to see what happens.

Good Luck.

好的，我已经阅读了所有的回复。你的问题是可怕的，它不是很清楚。根据评论，您要编写递归查询。您可以使用CTE，此处 [< a href =http://www.codeproject.com/Articles/702691/SQL-Wizardry-Part-Three-Common-Table-Expressions-Ctarget =_ blanktitle =New Window> ^ ]是关于它们如何工作的文章。如果您希望将该列表放入一个单元格，则需要使用Griff向您展示的XML方法，一旦您获得了可用的数据表。



我强烈怀疑你不会阅读任何文章，只是希望我们为你做所有的工作。那是不可能的。如果没有表格的模式，示例数据以及想要获得的结果示例，我们就无法做更多的事情，因此我们可以看到您想要的确切内容以及您想要的位置。

OK, I've read through all the replies. Your question is HORRIBLE, it's not remotely clear. According to the comments, you want to write a recursive query. You can do that using CTEs, here[^] is my article on how they work. If you then want to get that list in to one cell, you will need to use the XML approach Griff showed you, once you've got the table of data to work with.

I strongly suspect you're not going to read any articles and just want us to do all the work for you. That is impossible. We can't really do more without the schema of your table, example data, and examples of the results you want to get out, so we can see what exactly you want, and where you want it from.

这篇关于获取顾问的父ID列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！