大侠们,帮帮忙啊,如何才能按顺序列出表中的数据啊?
表数据在后面,如何才能显示
大概型如:
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);
[解决办法]
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 行受影响)
[解决办法]
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
...
*/
[解决办法]
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
*/
[解决办法]
hrb2008() ,你寫的不是樓主要的效果
