上下级的关系,订单与子订单的关系,谢谢各位高手.

marongc 2015-07-10 08:14:37
表T,有A和B两个字段

如:
A B
---------------------------------
111 [空]
222 111
333 222
444 111
555 [空]

我想要的结果
V1 V2
-------------------------------------
111 111
222 111/222
333 111/222/333
444 111/444
555 555

字段B指的是字段A的上级,最多只有三级,请大家帮忙 ,先谢谢大家了.

[code=sql][/
WITH T AS(
SELECT '111'AS A, ''AS B UNION ALL
SELECT '222'AS A, '111'AS B UNION ALL
SELECT '333'AS A, '222'AS B UNION ALL
SELECT '444'AS A, '111'AS B UNION ALL
SELECT '555'AS A, ''AS B
)

SELECT * FROM T
code]
...全文
404 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
Tiger_Zhao 2015-07-13
  • 打赏
  • 举报
回复
with T AS(
SELECT '111'AS A, ''AS B UNION ALL
SELECT '222'AS A, '111'AS B UNION ALL
SELECT '333'AS A, '222'AS B UNION ALL
SELECT '444'AS A, '111'AS B UNION ALL
SELECT '555'AS A, ''AS B
)
,tree AS(
SELECT *,
CONVERT(varchar(30),A) AS V2
FROM t
WHERE B=''
UNION ALL
SELECT t.*,
CONVERT(varchar(30),tree.V2+'\'+t.A) AS V2
FROM tree
JOIN t
ON tree.A = t.B
)
SELECT A AS V1,
V2
FROM tree
ORDER BY V2

V1   V2
---- ------------------------------
111 111
222 111\222
333 111\222\333
444 111\444
555 555
wf235y 2015-07-11
  • 打赏
  • 举报
回复
SELECT v4 AS v1  ,isnull(v1,'') 
	+ ' ' + isnull(v2,'') + ' ' + isnull(v3,'') AS v2 FROM 

(SELECT a AS v1 ,NULL AS v2,NULL AS v3,a AS v4 FROM #t1 WHERE len(isnull(b,''))=0

UNION
SELECT b AS v1,a AS v2,NULL AS v3,a AS v4 FROM #t1 WHERE b IN(SELECT a AS v1 FROM #t1 WHERE len(isnull(b,''))=0)

UNION

SELECT tt1.v1,tt1.v2,a AS v3,a AS v4 FROM #t1 
INNER JOIN 
(SELECT b AS v1,a AS v2,NULL AS v3 FROM #t1 WHERE b IN(SELECT a AS v1 FROM #t1 WHERE len(isnull(b,''))=0)) AS tt1
ON #t1.b=tt1.v2) AS tt2
lzw_0736 2015-07-11
  • 打赏
  • 举报
回复

--呵呵,上面都錯了,試試以下這個:
WITH T AS(
SELECT '111'AS A, ''AS B UNION ALL
SELECT '222'AS A, '111'AS B UNION ALL
SELECT '333'AS A, '222'AS B UNION ALL
SELECT '444'AS A, '111'AS B UNION ALL
SELECT '555'AS A, ''AS B 
)
,a1 AS
(
SELECT a,b,CAST(b+CASE WHEN b='' THEN '' ELSE '/' END+a AS VARCHAR(MAX)) c,1 d FROM t
UNION ALL
SELECT a1.a,t.b,CAST(t.b+'/'+a1.c AS VARCHAR(MAX)),d+1
FROM a1
JOIN t ON a1.b=t.a
WHERE t.b<>''
)
,a2 AS
(
SELECT a,MAX(d) d FROM a1 GROUP BY a
)
SELECT a1.a v1,a1.c v2
FROM a1
JOIN a2 ON a1.a=a2.a AND a1.d=a2.d
ORDER BY 1
lzw_0736 2015-07-11
  • 打赏
  • 举报
回复

--或者:
WITH T AS(
SELECT '111'AS A, ''AS B UNION ALL
SELECT '222'AS A, '111'AS B UNION ALL
SELECT '333'AS A, '222'AS B UNION ALL
SELECT '444'AS A, '111'AS B UNION ALL
SELECT '555'AS A, ''AS B 
)
,a1 AS
(
SELECT a,b,CAST(a+CASE WHEN b='' THEN '' ELSE '/' END+b AS VARCHAR(MAX)) c,1 d FROM t
UNION ALL
SELECT a1.a,t.b,CAST(a1.c+'/'+t.b AS VARCHAR(MAX)),d+1
FROM a1
JOIN t ON a1.b=t.a
WHERE t.b<>''
)
,a2 AS
(
SELECT a,MAX(d) d FROM a1 GROUP BY a
)
SELECT a1.a v1,REVERSE(a1.c) v2
FROM a1
JOIN a2 ON a1.a=a2.a AND a1.d=a2.d
ORDER BY 1
lzw_0736 2015-07-11
  • 打赏
  • 举报
回复

WITH T AS(
SELECT '111'AS A, ''AS B UNION ALL
SELECT '222'AS A, '111'AS B UNION ALL
SELECT '333'AS A, '222'AS B UNION ALL
SELECT '444'AS A, '111'AS B UNION ALL
SELECT '555'AS A, ''AS B 
)
,a1 AS
(
SELECT a,b,CAST(a+'/'+b AS VARCHAR(MAX)) c,1 d FROM t WHERE b<>''
UNION ALL
SELECT a1.a,t.b,CAST(a1.c+'/'+t.b AS VARCHAR(MAX)),d+1
FROM a1
JOIN t ON a1.b=t.a
WHERE t.b<>''
)
,a2 AS
(
SELECT a,MAX(d) d FROM a1 GROUP BY a
)
SELECT a1.a v1,REVERSE(a1.c) v2
FROM a1
JOIN a2 ON a1.a=a2.a AND a1.d=a2.d
UNION ALL
SELECT a,a FROM t WHERE b=''
ORDER BY 1
marongc 2015-07-11
  • 打赏
  • 举报
回复
是不是很难啊,呵呵

22,301

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧