希望用SQL实现,如果有好的算法也可以推荐。
--------------------------------------
元组是这样的
CID,A,B,C,D,E 这是一行,先是这么六列。【也可能变成A,B,C,D,E,F,G。这么多列。这就是后话了。先把六个的解决在说。如果谁把这个不限制列数目的也解决了,那O在CSDN放分7天,答对的人回帖优先得分】希望返回如下数据【认为前一个元素就是父节点,临接的下一个就是子节点】CID,A,B
CID,B,C
CID, C,D
CID, D,E
-----------
A,B,C,D,E都是可空类型,如果其中一个是空就跳过,比如B是空就返回CID,A,C
CID, C,D
CID, D,EA是空就返回
CID,B,C
CID, C,D
CID, D,EE是空就返回
CID,A,B
CID,B,C
CID, C,D这问题100分少点,但是这个区我就能放100分。
--------------------------------------
元组是这样的
CID,A,B,C,D,E 这是一行,先是这么六列。【也可能变成A,B,C,D,E,F,G。这么多列。这就是后话了。先把六个的解决在说。如果谁把这个不限制列数目的也解决了,那O在CSDN放分7天,答对的人回帖优先得分】希望返回如下数据【认为前一个元素就是父节点,临接的下一个就是子节点】CID,A,B
CID,B,C
CID, C,D
CID, D,E
-----------
A,B,C,D,E都是可空类型,如果其中一个是空就跳过,比如B是空就返回CID,A,C
CID, C,D
CID, D,EA是空就返回
CID,B,C
CID, C,D
CID, D,EE是空就返回
CID,A,B
CID,B,C
CID, C,D这问题100分少点,但是这个区我就能放100分。
CID,B,C
CID, C,D
CID, D,E
CID, B,F这种情况B是NULL结果如何?
1、表
2、变量 set @s='CID,A,B,C,D,E'你这个放哪里测试?
CID,A,B,C,D,E是一个元组,其中的就是元素。元组就是表中的行呀,A,C...就是列。
insert tb select 'AID','A','B','C',NULL,'E'
insert tb select 'BID',NULL,'B','C','D','E'
insert tb select 'CID','A','B','C','D','E'
insert tb select 'DID','A','B','C','D',NULL
go;with t1 as
(
select * from tb a unpivot (v for name in ([c1],[c2],[c3],[c4],[c5])) b
)
,t2 as
(
select id = row_number() over(partition by c0 order by b.column_id),c0,v
from t1 a
join (select name,column_id from sys.columns where object_id = object_id('tb')) b on a.name = b.name
)
select a.c0,a.v,b.v from t2 a join t2 b on a.id = b.id - 1 and a.c0 = b.c0/*
c0 v v
---------- ---------- ----------
AID A B
AID B C
AID C E
BID B C
BID C D
BID D E
CID A B
CID B C
CID C D
CID D E
DID A B
DID B C
DID C D(13 行受影响)
*/
不知道这是不是楼主想要的效果。列数不限也可以照此办理,只是写成动态而已
(
CateNo nvarchar(20),
CateName nvarchar(20),
PCateNo nvarchar(20)
)
insert into @tb(CateNo,CateName,PCateNo) select CateNo,CateName,PCateNo from dbo.Test_CateList
;WITH t AS
(
SELECT CateNo,CateName,PCateNo,PATH = CAST(CateNo AS NVARCHAR)
FROM @tb AS A
WHERE NOT EXISTS(SELECT null FROM @tb WHERE CateNo= A.PCateNo)
UNION ALL
SELECT A.CateNo , A.PCateNo , A.CateName, CAST(B.PATH+'-->'+A.CateNo AS NVARCHAR)
FROM @tb AS A JOIN t AS B ON A.PCateNo = B.CateNo
)
SELECT t.* FROM t ORDER BY CateNo因为CTE方式IO消耗过多,时间过长,所以我要等待更好的方式。
---------------------------------------------------------------------------- Author : htl258(Tony)-- Date : 2010-04-23 02:37:28-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)-- Subject: BOM按节点排序应用实例-------------------------------------------------------------------------- --实例1:--> 生成测试数据表:tb IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb]GOCREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))INSERT [tb]SELECT 1,'01',0,N'服装' UNION ALLSELECT 2,'01',1,N'男装' UNION ALLSELECT 3,'01',2,N'西装' UNION ALLSELECT 4,'01',3,N'全毛' UNION ALLSELECT 5,'02',3,N'化纤' UNION ALLSELECT 6,'02',2,N'休闲装' UNION ALLSELECT 7,'02',1,N'女装' UNION ALLSELECT 8,'01',7,N'套装' UNION ALLSELECT 9,'02',7,N'职业装' UNION ALLSELECT 10,'03',7,N'休闲装' UNION ALLSELECT 11,'04',7,N'西装' UNION ALLSELECT 12,'01',11,N'全毛' UNION ALLSELECT 13,'02',11,N'化纤' UNION ALLSELECT 14,'05',7,N'休闲装'GO--SELECT * FROM [tb] -->SQL查询如下: ;WITH T AS( SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*, CAST(ID AS VARBINARY(MAX)) AS px FROM tb AS A WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid) UNION ALL SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*, CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX)) FROM tb AS A JOIN T AS B ON A.pid=B.id)SELECT Code,Name FROM T ORDER BY px/*Code Name-------------------- ----------01 服装0101 男装010101 西装01010101 全毛01010102 化纤010102 休闲装0102 女装010201 套装010202 职业装010203 休闲装010204 西装01020401 全毛01020402 化纤010205 休闲装 (14 行受影响)*/ --实例2:--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))
INSERT [tb]
SELECT 1,0,'test1' UNION ALL
SELECT 2,0,'test2' UNION ALL
SELECT 3,1,'test1.1' UNION ALL
SELECT 4,2,'test2.1' UNION ALL
SELECT 5,3,'test1.1.1' UNION ALL
SELECT 6,1,'test1.2'
GO
--SELECT * FROM [tb]-->SQL查询如下:
;WITH T AS
(
SELECT *,CAST(ID AS VARBINARY(MAX)) AS px
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid])
UNION ALL
SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM tb AS A
JOIN T AS B
ON A.[parentid]=B.id
)
SELECT [id],[parentid],[categoryname] FROM T
ORDER BY px
/*
id parentid categoryname
----------- ----------- ------------
1 0 test1
3 1 test1.1
5 3 test1.1.1
6 1 test1.2
2 0 test2
4 2 test2.1(6 行受影响)
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx
as
(select c0,v,ROW_NUMBER() over(partition by c0 order by v) id
from tb a unpivot (v for name in ([c1],[c2],[c3],[c4],[c5])) b)
select a.c0,a.v,b.v from cte a,cte b
where a.c0=b.c0
and a.id=b.id-1
insert tb select 'AID','A','B','C',NULL,'E'
insert tb select 'BID',NULL,'B','C','D','E'
insert tb select 'CID','A','B','C','D','E'
insert tb select 'DID','A','B','C','D',NULLwith cte
as
(select c0,v,ROW_NUMBER() over(partition by c0 order by v) id
from tb a unpivot (v for name in ([c1],[c2],[c3],[c4],[c5])) b)
select a.c0,a.v,b.v from cte a,cte b
where a.c0=b.c0
and a.id=b.id-1