create table #t1 (
[mID] int
, [mParent] int
, [mSeq] int
, [mTitle] varchar(64) )
insert into #t1 SELECT 1, 0, 1, 'root'
insert into #t1 SELECT 2, 1, 1, '1'
insert into #t1 SELECT 3, 2, 1, '1.1'
insert into #t1 SELECT 4, 2, 2, '1.2'
insert into #t1 SELECT 5, 2, 3, '1.3'
insert into #t1 SELECT 6, 1, 2, '2'
insert into #t1 SELECT 7, 6, 1, '2.1'
insert into #t1 SELECT 8, 6, 2, '2.2'
insert into #t1 SELECT 9, 1, 3, '3'
insert into #t1 SELECT 10, 9, 1, '3.1'
insert into #t1 SELECT 11, 10, 1, '3.1.1'
insert into #t1 SELECT 12, 9, 2, '3.2'
insert into #t1 SELECT 13, 4, 1, '1.2.1'
WITH cte AS (
SELECT mID, mTitle, mSeq= CONVERT(bigint, mSeq), mParent
, mLv= CONVERT(bigint, 1), mScore= CONVERT(bigint, 1)
FROM #t1 WHERE mParent= 0
UNION ALL
SELECT c.mID, c.mTitle, mSeq= CONVERT(bigint, c.mSeq), c.mParent
, mLv= CONVERT(bigint, p.mLv+1), mScore= CONVERT(bigint, p.mScore+c.mSeq*POWER(100, p.mLv))
FROM #t1 AS c
, cte AS p
WHERE c.mParent= p.mID
)
SELECT rowNo= ROW_NUMBER() OVER (ORDER BY x.mScore asc)
, x.mID, x.mTitle, x.mLv, x.mSeq
FROM (
SELECT mID, mTitle, mSeq, mParent, mLv
, mScore= CONVERT(bigint, REVERSE(RIGHT('0000000000000000'+CONVERT(varchar(16), mScore), 16)))
FROM cte) AS x
-- 參考
http://www.mssqltips.com/tip.asp?tip=1939 |
發表於 2010-7-23 10:17
|