亲子关系 - SQL查询

我的源是如下

OSPID OSPNAME RELATEDOSPID 100004 LEVEL4 100003 100003 LEVEL3 100002 100002 LEVEL2 100001 100001 LEVEL1 0 100009 LEVEL4 100008 100008 LEVEL2 100007

我需要的结果作为

L4OSPID L4OSPNAME L3RELATEDID L3OSPNAME L2RELATEDID L2OSPNAME L1RELATEDID L2OSPNAME ROOTNODE 100004 LEVEL4 100003 LEVEL3 100002 LEVEL2 100001 LEVEL1 0 100009 LEVEL4 100008 NULL NULL LEVEL2 100007 NULL NULL

如果二者之间的任何级别的缺失应该把空

谢谢

--------------解决方案-------------

假设只有4级,你总是想从LEVEL4开始,这里是查询

select L4.OSPID as L4OSPID, L4.OSPNAME as L4OPSNAME,
L4.RELATEDOSPID
as L3RELATEDID,

case
WHEN L3.OSPNAME = 'LEVEL3' THEN L3.OSPNAME
ELSE NULL
end as L3OSPNAME,

case
WHEN L3.OSPNAME = 'LEVEL3' THEN L3.RELATEDOSPID
ELSE NULL
end as L2RELATEDID,

case
WHEN L3.OSPNAME = 'LEVEL2' THEN L3.OSPNAME
WHEN L2.OSPNAME = 'LEVEL2' THEN L2.OSPNAME
ELSE NULL
end as L2OSPNAME,

case WHEN L3.OSPNAME = 'LEVEL2' THEN L3.RELATEDOSPID
WHEN L2.OSPNAME = 'LEVEL2' THEN L2.RELATEDOSPID
ELSE NULL
end
as L1RELATEDID,

case
WHEN L3.OSPNAME = 'LEVEL1' THEN L3.OSPNAME
WHEN L2.OSPNAME = 'LEVEL1' THEN L2.OSPNAME
WHEN L1.OSPNAME = 'LEVEL1' THEN L1.OSPNAME
ELSE NULL
end as L1OSPNAME,

case
WHEN L3.OSPNAME = 'LEVEL1' THEN L3.RELATEDOSPID
WHEN L2.OSPNAME = 'LEVEL1' THEN L2.RELATEDOSPID
WHEN L1.OSPNAME = 'LEVEL1' THEN L1.RELATEDOSPID
ELSE NULL
end as ROOTNODE

from osptable as L4

left outer join osptable as L3
on L4.RELATEDOSPID = L3.OSPID
left outer join osptable as L2
on L3.RELATEDOSPID = L2.OSPID
left outer join osptable as L1
on L2.RELATEDOSPID = L1.OSPID
where L4.OSPNAME = 'LEVEL4'

这给了我这样的结果,我认为这是一个希望的

100004 LEVEL4 100003 LEVEL3 100002 LEVEL2 100001 LEVEL1 0
100009 LEVEL4 100008 NULL NULL LEVEL2 100007 NULL NULL

分类:SQL服务器 时间:2015-03-16 人气:260
本文关键词: SQL Server中,TSQL
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 1.098 (s). 11 q(s)