经典的树表设计大概是这样:
ID, Name, ParentID
都在一个表里.
要取出这棵树,无非就是用存储过程,或在程序里递归.
一般推荐用存储过程来做,因为要是放在程序里用递归,要查询很多次的,没有一点效率.
前几天研究了用 WITH 实现递归, 今天我在写个用 WITH 读取一棵树的方法.
先看效果:
结果:
Electronics
---Home Audio Systems
------Micro Systems
------Mini Systems
------Amplifiers & Receivers
------Speakers
------Home Theaters
---MP3 Players
---Multimedia Players
---DVD Players & Recorders
------Digital Voice Recorders
------DVD Recorders
------Portable DVD Players
------DVD & VCR Combo Players
------Set Top Boxes
------DVD players
---Projectors
---Unclassified
---GPS
---Portable Audio
------Cassette Players
------CD Players
------Portable CD Players
------Headphones
------Clock Radios
------Portable Radios
------Earphones
---Electronics Accessories
------Home Theater Systems Accessories
------Headphones Accessories
------Earphones Accessories
------Car Audio Accessories
------MP3 Players Accessories
---------Cases
---------Docking Stations
---------FM Transmitters
---------MP3 Headphones
---------MP3 Remote Controls
---------Speaker Systems
------DVD Players Accessories
------Speakers Accessories
------Projectors Accessories
------Scanners Accessories
------GPS Accessories
Music
---Alternative Rock
---Blues
---Children's Music
---Gospel
---Classical
...
...
...
看语句:
WITH CatTree AS (
SELECT
CategoryID, CategoryName, ParentID,
0 AS L,
CAST(ParentID AS VARCHAR) + ',' + CAST(CategoryID AS VARCHAR) AS Tag
FROM
CSK_Store_Category
WHERE
ParentID = 0
UNION ALL
SELECT
C.CategoryID, C.CategoryName, C.ParentID,
CatTree.L + 1 AS L,
CAST(CatTree.Tag AS VARCHAR) + ',' + CAST( C.CategoryID AS VARCHAR) AS Tag
FROM
CSK_Store_Category C INNER JOIN
CatTree ON C.ParentID = CatTree.CategoryID
)
SELECT
*,
REPLACE(RIGHT(POWER(10, CatTree.L),CatTree.L),'0','---') + CategoryName
FROM
CatTree ORDER BY Tag
SQLServer 里没有诸如 LPAD, RPAD 这样的函数, 重复一个字符串还有点小技巧:
REPLACE(RIGHT(POWER(10, CatTree.L),CatTree.L),'0','---') + CategoryName
另外,让我不解的是我把上面代码里的 连接符号(,) 换成 (-) , 读出来的顺序居然错的离谱,如图:
| Next > |
|---|