大侠们,帮帮忙啊,如何才能按顺序列出表中的数据啊?

jamfchan 2007-04-10 10:53:52
表数据在后面,如何才能显示
大概型如:
ID
3
38
39
53
5
23
24
25
53
54
55
56
57
58
77
7
9
21
100
132
133

前面的空格不管它,主要是这样树型的顺序,即某一项的位置先比较其祖先元素的排序,同等的再比较自身.
谢谢
/********************

CREATE TABLE `test` (
`categories_id` int(6) NOT NULL default '0',
`parent_id` int(6) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `test` VALUES (3, 0);
INSERT INTO `test` VALUES (5, 0);
INSERT INTO `test` VALUES (7, 0);
INSERT INTO `test` VALUES (9, 0);
INSERT INTO `test` VALUES (102, 9);
INSERT INTO `test` VALUES (14, 5);
INSERT INTO `test` VALUES (97, 131);
INSERT INTO `test` VALUES (114, 106);
INSERT INTO `test` VALUES (85, 144);
INSERT INTO `test` VALUES (86, 0);
INSERT INTO `test` VALUES (21, 9);
INSERT INTO `test` VALUES (22, 131);
INSERT INTO `test` VALUES (23, 5);
INSERT INTO `test` VALUES (24, 5);
INSERT INTO `test` VALUES (25, 5);
INSERT INTO `test` VALUES (34, 144);
INSERT INTO `test` VALUES (104, 131);
INSERT INTO `test` VALUES (38, 3);
INSERT INTO `test` VALUES (39, 3);
INSERT INTO `test` VALUES (100, 9);
INSERT INTO `test` VALUES (44, 158);
INSERT INTO `test` VALUES (46, 131);
INSERT INTO `test` VALUES (47, 141);
INSERT INTO `test` VALUES (48, 131);
INSERT INTO `test` VALUES (49, 140);
INSERT INTO `test` VALUES (50, 131);
INSERT INTO `test` VALUES (51, 131);
INSERT INTO `test` VALUES (52, 9);
INSERT INTO `test` VALUES (53, 5);
INSERT INTO `test` VALUES (54, 5);
INSERT INTO `test` VALUES (55, 5);
INSERT INTO `test` VALUES (56, 5);
INSERT INTO `test` VALUES (57, 5);
INSERT INTO `test` VALUES (58, 5);
INSERT INTO `test` VALUES (96, 146);
INSERT INTO `test` VALUES (87, 131);
INSERT INTO `test` VALUES (88, 145);
INSERT INTO `test` VALUES (89, 145);
INSERT INTO `test` VALUES (90, 144);
INSERT INTO `test` VALUES (91, 145);
INSERT INTO `test` VALUES (106, 0);
INSERT INTO `test` VALUES (98, 9);
INSERT INTO `test` VALUES (99, 0);
INSERT INTO `test` VALUES (77, 5);
INSERT INTO `test` VALUES (93, 92);
INSERT INTO `test` VALUES (81, 131);
INSERT INTO `test` VALUES (82, 9);
INSERT INTO `test` VALUES (83, 137);
INSERT INTO `test` VALUES (107, 0);
INSERT INTO `test` VALUES (108, 0);
INSERT INTO `test` VALUES (109, 0);
INSERT INTO `test` VALUES (110, 0);
INSERT INTO `test` VALUES (111, 0);
INSERT INTO `test` VALUES (112, 0);
INSERT INTO `test` VALUES (113, 0);
INSERT INTO `test` VALUES (115, 106);
INSERT INTO `test` VALUES (116, 106);
INSERT INTO `test` VALUES (117, 107);
INSERT INTO `test` VALUES (118, 107);
INSERT INTO `test` VALUES (119, 113);
INSERT INTO `test` VALUES (120, 109);
INSERT INTO `test` VALUES (121, 109);
INSERT INTO `test` VALUES (122, 110);
INSERT INTO `test` VALUES (157, 110);
INSERT INTO `test` VALUES (124, 108);
INSERT INTO `test` VALUES (125, 145);
INSERT INTO `test` VALUES (126, 85);
INSERT INTO `test` VALUES (127, 85);
INSERT INTO `test` VALUES (128, 85);
INSERT INTO `test` VALUES (129, 85);
INSERT INTO `test` VALUES (130, 85);
INSERT INTO `test` VALUES (131, 9);
INSERT INTO `test` VALUES (132, 100);
INSERT INTO `test` VALUES (133, 100);
INSERT INTO `test` VALUES (134, 82);
INSERT INTO `test` VALUES (135, 82);
INSERT INTO `test` VALUES (136, 82);
INSERT INTO `test` VALUES (137, 140);
INSERT INTO `test` VALUES (138, 137);
INSERT INTO `test` VALUES (139, 137);
INSERT INTO `test` VALUES (140, 9);
INSERT INTO `test` VALUES (141, 9);
INSERT INTO `test` VALUES (142, 141);
INSERT INTO `test` VALUES (143, 141);
INSERT INTO `test` VALUES (144, 9);
INSERT INTO `test` VALUES (145, 144);
INSERT INTO `test` VALUES (146, 9);
INSERT INTO `test` VALUES (147, 146);
INSERT INTO `test` VALUES (148, 146);
INSERT INTO `test` VALUES (149, 146);
INSERT INTO `test` VALUES (150, 91);
INSERT INTO `test` VALUES (151, 91);
INSERT INTO `test` VALUES (152, 91);
INSERT INTO `test` VALUES (153, 125);
INSERT INTO `test` VALUES (154, 125);
INSERT INTO `test` VALUES (155, 146);
INSERT INTO `test` VALUES (156, 106);
INSERT INTO `test` VALUES (158, 131);
INSERT INTO `test` VALUES (159, 158);
INSERT INTO `test` VALUES (160, 108);
INSERT INTO `test` VALUES (161, 108);
INSERT INTO `test` VALUES (162, 145);
INSERT INTO `test` VALUES (163, 110);
INSERT INTO `test` VALUES (164, 110);
INSERT INTO `test` VALUES (165, 110);
INSERT INTO `test` VALUES (166, 113);
...全文
208 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
paoluo 2007-04-10
  • 打赏
  • 举报
 回复
hrb2008() ,你寫的不是樓主要的效果
hrb2008 2007-04-10
  • 打赏
  • 举报
 回复
CREATE TABLE test (
categories_id int NOT NULL default 0,
parent_id int NOT NULL
)
INSERT INTO test VALUES (3, 0)
INSERT INTO test VALUES (5, 0)
INSERT INTO test VALUES (7, 0)
INSERT INTO test VALUES (9, 0)
INSERT INTO test VALUES (102, 9)
INSERT INTO test VALUES (14, 5)
INSERT INTO test VALUES (97, 131)
INSERT INTO test VALUES (114, 106)
INSERT INTO test VALUES (85, 144)
INSERT INTO test VALUES (86, 0)
INSERT INTO test VALUES (21, 9)
INSERT INTO test VALUES (22, 131)
INSERT INTO test VALUES (23, 5)
INSERT INTO test VALUES (24, 5)
INSERT INTO test VALUES (25, 5)
INSERT INTO test VALUES (34, 144)
INSERT INTO test VALUES (104, 131)
INSERT INTO test VALUES (38, 3)
INSERT INTO test VALUES (39, 3)
INSERT INTO test VALUES (100, 9)
INSERT INTO test VALUES (44, 158)
INSERT INTO test VALUES (46, 131)
INSERT INTO test VALUES (47, 141)
INSERT INTO test VALUES (48, 131)
INSERT INTO test VALUES (49, 140)
INSERT INTO test VALUES (50, 131)
INSERT INTO test VALUES (51, 131)
INSERT INTO test VALUES (52, 9)
INSERT INTO test VALUES (53, 5)
INSERT INTO test VALUES (54, 5)
INSERT INTO test VALUES (55, 5)
INSERT INTO test VALUES (56, 5)
INSERT INTO test VALUES (57, 5)
INSERT INTO test VALUES (58, 5)
INSERT INTO test VALUES (96, 146)
INSERT INTO test VALUES (87, 131)
INSERT INTO test VALUES (88, 145)
INSERT INTO test VALUES (89, 145)
INSERT INTO test VALUES (90, 144)
INSERT INTO test VALUES (91, 145)
INSERT INTO test VALUES (106, 0)
INSERT INTO test VALUES (98, 9)
INSERT INTO test VALUES (99, 0)
INSERT INTO test VALUES (77, 5)
INSERT INTO test VALUES (93, 92)
INSERT INTO test VALUES (81, 131)
INSERT INTO test VALUES (82, 9)
INSERT INTO test VALUES (83, 137)
INSERT INTO test VALUES (107, 0)
INSERT INTO test VALUES (108, 0)
INSERT INTO test VALUES (109, 0)
INSERT INTO test VALUES (110, 0)
INSERT INTO test VALUES (111, 0)
INSERT INTO test VALUES (112, 0)
INSERT INTO test VALUES (113, 0)
INSERT INTO test VALUES (115, 106)
INSERT INTO test VALUES (116, 106)
INSERT INTO test VALUES (117, 107)
INSERT INTO test VALUES (118, 107)
INSERT INTO test VALUES (119, 113)
INSERT INTO test VALUES (120, 109)
INSERT INTO test VALUES (121, 109)
INSERT INTO test VALUES (122, 110)
INSERT INTO test VALUES (157, 110)
INSERT INTO test VALUES (124, 108)
INSERT INTO test VALUES (125, 145)
INSERT INTO test VALUES (126, 85)
INSERT INTO test VALUES (127, 85)
INSERT INTO test VALUES (128, 85)
INSERT INTO test VALUES (129, 85)
INSERT INTO test VALUES (130, 85)
INSERT INTO test VALUES (131, 9)
INSERT INTO test VALUES (132, 100)
go
DECLARE @t_Level TABLE(categories_id int,Level int)
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT categories_id,@Level
FROM test
WHERE parent_id =0
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.categories_id,@Level
FROM test a,@t_Level b
WHERE a.parent_id=b.categories_id
AND b.Level=@Level-1
END
select * from @t_Level
drop TABLE test
/*
categories_id Level
------------- -----------
3 0
5 0
7 0
9 0
86 0
106 0
99 0
107 0
108 0
109 0
110 0
111 0
112 0
113 0
38 1
39 1
23 1
24 1
25 1
14 1
53 1
54 1
55 1
56 1
57 1
58 1
77 1
98 1
82 1
131 1
52 1
21 1
102 1
100 1
115 1
116 1
114 1
117 1
118 1
124 1
120 1
121 1
122 1
157 1
119 1
132 2
81 2
97 2
22 2
46 2
48 2
50 2
51 2
104 2
87 2
*/
paoluo 2007-04-10
  • 打赏
  • 举报
 回复
CREATE TABLE test (
categories_id int NOT NULL default '0',
parent_id int NOT NULL default '0'
)
INSERT INTO test VALUES (3, 0);
INSERT INTO test VALUES (5, 0);
INSERT INTO test VALUES (7, 0);
INSERT INTO test VALUES (9, 0);
INSERT INTO test VALUES (102, 9);
INSERT INTO test VALUES (14, 5);
INSERT INTO test VALUES (97, 131);
INSERT INTO test VALUES (114, 106);
INSERT INTO test VALUES (85, 144);
INSERT INTO test VALUES (86, 0);
INSERT INTO test VALUES (21, 9);
INSERT INTO test VALUES (22, 131);
INSERT INTO test VALUES (23, 5);
INSERT INTO test VALUES (24, 5);
INSERT INTO test VALUES (25, 5);
INSERT INTO test VALUES (34, 144);
INSERT INTO test VALUES (104, 131);
INSERT INTO test VALUES (38, 3);
INSERT INTO test VALUES (39, 3);
INSERT INTO test VALUES (100, 9);
INSERT INTO test VALUES (44, 158);
INSERT INTO test VALUES (46, 131);
INSERT INTO test VALUES (47, 141);
INSERT INTO test VALUES (48, 131);
INSERT INTO test VALUES (49, 140);
INSERT INTO test VALUES (50, 131);
INSERT INTO test VALUES (51, 131);
INSERT INTO test VALUES (52, 9);
INSERT INTO test VALUES (53, 5);
INSERT INTO test VALUES (54, 5);
INSERT INTO test VALUES (55, 5);
INSERT INTO test VALUES (56, 5);
INSERT INTO test VALUES (57, 5);
INSERT INTO test VALUES (58, 5);
INSERT INTO test VALUES (96, 146);
INSERT INTO test VALUES (87, 131);
INSERT INTO test VALUES (88, 145);
INSERT INTO test VALUES (89, 145);
INSERT INTO test VALUES (90, 144);
INSERT INTO test VALUES (91, 145);
INSERT INTO test VALUES (106, 0);
INSERT INTO test VALUES (98, 9);
INSERT INTO test VALUES (99, 0);
INSERT INTO test VALUES (77, 5);
INSERT INTO test VALUES (93, 92);
INSERT INTO test VALUES (81, 131);
INSERT INTO test VALUES (82, 9);
INSERT INTO test VALUES (83, 137);
INSERT INTO test VALUES (107, 0);
INSERT INTO test VALUES (108, 0);
INSERT INTO test VALUES (109, 0);
INSERT INTO test VALUES (110, 0);
INSERT INTO test VALUES (111, 0);
INSERT INTO test VALUES (112, 0);
INSERT INTO test VALUES (113, 0);
INSERT INTO test VALUES (115, 106);
INSERT INTO test VALUES (116, 106);
INSERT INTO test VALUES (117, 107);
INSERT INTO test VALUES (118, 107);
INSERT INTO test VALUES (119, 113);
INSERT INTO test VALUES (120, 109);
INSERT INTO test VALUES (121, 109);
INSERT INTO test VALUES (122, 110);
INSERT INTO test VALUES (157, 110);
INSERT INTO test VALUES (124, 108);
INSERT INTO test VALUES (125, 145);
INSERT INTO test VALUES (126, 85);
INSERT INTO test VALUES (127, 85);
INSERT INTO test VALUES (128, 85);
INSERT INTO test VALUES (129, 85);
INSERT INTO test VALUES (130, 85);
INSERT INTO test VALUES (131, 9);
INSERT INTO test VALUES (132, 100);
INSERT INTO test VALUES (133, 100);
INSERT INTO test VALUES (134, 82);
INSERT INTO test VALUES (135, 82);
INSERT INTO test VALUES (136, 82);
INSERT INTO test VALUES (137, 140);
INSERT INTO test VALUES (138, 137);
INSERT INTO test VALUES (139, 137);
INSERT INTO test VALUES (140, 9);
INSERT INTO test VALUES (141, 9);
INSERT INTO test VALUES (142, 141);
INSERT INTO test VALUES (143, 141);
INSERT INTO test VALUES (144, 9);
INSERT INTO test VALUES (145, 144);
INSERT INTO test VALUES (146, 9);
INSERT INTO test VALUES (147, 146);
INSERT INTO test VALUES (148, 146);
INSERT INTO test VALUES (149, 146);
INSERT INTO test VALUES (150, 91);
INSERT INTO test VALUES (151, 91);
INSERT INTO test VALUES (152, 91);
INSERT INTO test VALUES (153, 125);
INSERT INTO test VALUES (154, 125);
INSERT INTO test VALUES (155, 146);
INSERT INTO test VALUES (156, 106);
INSERT INTO test VALUES (158, 131);
INSERT INTO test VALUES (159, 158);
INSERT INTO test VALUES (160, 108);
INSERT INTO test VALUES (161, 108);
INSERT INTO test VALUES (162, 145);
INSERT INTO test VALUES (163, 110);
INSERT INTO test VALUES (164, 110);
INSERT INTO test VALUES (165, 110);
INSERT INTO test VALUES (166, 113);

--Select * From test
GO
--深度排序显示处理
--生成每个节点的编码累计
DECLARE @t_Level TABLE(categories_id Int, parent_id Int, Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT categories_id, categories_id, @Level,',' + Rtrim(categories_id)
FROM test
WHERE parent_id = 0
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.categories_id, b.parent_id, @Level,b.Sort+',' + Rtrim(a.categories_id)
FROM test a,@t_Level b
WHERE a.parent_id=b.categories_id
AND b.Level=@Level-1
END

--显示结果
SELECT SPACE(b.Level *2) + Rtrim(a.categories_id) As categories_id
FROM test a,@t_Level b
WHERE a.categories_id=b.categories_id
ORDER BY b.parent_id, b.Sort
GO
Drop Table test
--Result
/*
categories_id
3
38
39
5
14
23
24
25
53
54
55
56
57
58
77
7
9
100
132
133
102
131
104
158
159
44
22
46
48
50
51
81
87
97
140
137
138
139
83
49
141
142
143
47
144
145
125
153
154
162
88
89
91
150
151
152
34
85
126
127
128
129
130
90
146
147
148
149
155
96
21
52
82
134
135
136
98
86
...
*/


jamfchan 2007-04-10
  • 打赏
  • 举报
 回复
楼上的误解我的意思了,是我没说好,
我想说的是,像这种树型结构的数据,例如
1
2 3
4 5 6 7
8 9 10 11
显示顺序为,1,2,4,8,5,3,6,9,10,7,11

谢谢
中国风 2007-04-10
  • 打赏
  • 举报
 回复
declare @ta table (id int)
insert @ta select 1
union select 10
union select 100
union select 102
union select 132


select id=case when id!>10 then rtrim(id) when id between 11 and 100 then space(1)+rtrim(id)
else space(3)++rtrim(id) end
from @ta


(5 行受影响)
id
---------------
1
10
100
102
132

(5 行受影响)

hrb2008 2007-04-10
  • 打赏
  • 举报
 回复
：（呜呜.....
jamfchan 2007-04-10
  • 打赏
  • 举报
 回复
非常感谢 paoluo(一天到晚游泳的鱼)

C# For Unity系列之入门篇
你想当“李逍遥”式的“大侠”吗？ 这里无需计算机基础，无需编程经验，你也不必是计算机专业的在校大学生....只要爱好游戏，怀揣梦想！ 有一定自主学习能力，跟着刘老师从“编程小白”修炼为游戏研发“大虾”吧！！...
网上如何提问才能获得好的回答？
首先你应该明白，尽管大侠们有蔑视简单问题和不友善的坏名声，有时看起来似乎他们对新手，对知识贫乏者怀有敌意，但事实上并不是这样的。 <br /> 他们不想掩饰一种鄙视——某些人不愿思考，或者在发问前不去...
java基础002
试卷一2013年年底的时候，我看到了网上流传的一个叫做《Java面试题大全》的东西，认真的阅读...在修订的过程中，参照了当时JDK最新版本（Java 7）给出了题目的答案和相关代码，去掉了EJB 2.x、JSF等无用内容或过时内...
基础试题
基础部分的顺序：基本语法，类相关的语法，内部类的语法，继承相关的语法，异常的语法，线程的语法，集合的语法，io的语法，虚拟机方面的语法。 1、一个".java"源文件中是否可以包括多个类（不是内部类）？有什么...
java面试题
基础部分的顺序：基本语法，类相关的语法，内部类的语法，继承相关的语法，异常的语法，线程的语法，集合的语法，io 的语法，虚拟机方面的语法。 1、一个".java"源文件中是否可以包括多个类（不是内部类）？有什么...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章