如何实现以下输出 [英] How Do I Achieve Following Output
问题描述
亲爱的团队,
我有一张桌子......单位大师(IN SQL 2005)
Unit_id Unit Link_id
1 Kg 2
2 gm 3
3 mg 0
4 DZN 5
5分0
输出我需要输出的所有行如果有链接通过单个查询以任何方式将父母与孩子或孩子的父母联系到父母...有没有办法在单个查询中实现?
如果传入proc,则输出参数1/2/3
Unit_id单位Link_id
1 Kg 2
2 gm 3
3 mg 0
如果传入proc参数输出4/5
Unit_id Unit Link_id
4 DZN 5
5 Pcs 0
谢谢
Sukhen Dass
Dear Team,
I have a table....Unit Master(IN SQL 2005)
Unit_id Unit Link_id
1 Kg 2
2 gm 3
3 mg 0
4 DZN 5
5 Pcs 0
in output i need all the rows come in the output if they have link in any way parent to child or child to parent through a single query...is there any way to achieve in a single query ?
Output if pass in a proc parameter 1/2/3
Unit_id Unit Link_id
1 Kg 2
2 gm 3
3 mg 0
Output if pass in a proc parameter 4/5
Unit_id Unit Link_id
4 DZN 5
5 Pcs 0
Thanks
Sukhen Dass
推荐答案
尝试:
Try:
SELECT Unitid, UnitLink
FROM UnitMaster
WHERE Unitid IN (1,2,3)
create table parents(unit_id bigint identity(1,1),unit nvarchar(max),link_id bigint)
insert into parents values('kg',2)
insert into parents values('gm',3)
insert into parents values('mg',0)
insert into parents values('DZN',5)
insert into parents values('pcs',0)
只传递parent_id:
just pass the parent_id :
with cte (link_id,unit_id,unit)
as
(
select link_id,unit_id,unit from parents where unit_id=1
union all
select b.link_id,b.unit_id,b.unit from cte as a inner join parents as b on a.link_id=b.unit_id
)
select unit_id,link_id,unit From cte
with cte (link_id,unit_id,unit)
as
(
select link_id,unit_id,unit from parents where unit_id=4
union all
select b.link_id,b.unit_id,b.unit from cte as a inner join parents as b on a.link_id=b.unit_id
)
select unit_id,link_id,unit From cte
这篇关于如何实现以下输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!