跪求此数据的排序SQL语句

Question

数据库（MySQL）表结构如下：
ID(int) PID（int） NAME（varchar） WBS（varchar）    WBS_TAIL（int）
  1         0         A                     1.1             1
  2         1         A-A                   1.1.1           1
  3         1         A-B                   1.1.2           2
  4         2         A-A-A                 1.1.1.1         1
  5         2         A-A-B                 1.1.1.2         2
  6         2         A-A-C                 1.1.1.3         3
  7         2         A-A-D                 1.1.1.4         4
  8         2         A-A-E                 1.1.1.5         5
  9         2         A-A-F                 1.1.1.6         6
  10        2         A-A-G                 1.1.1.7         7
  11        2         A-A-H                 1.1.1.8         8
  12        2         A-A-I                 1.1.1.9         9
  13        2         A-A-J                 1.1.1.10        10
  14        2         A-A-K                 1.1.1.11        11
  15        2         A-A-L                 1.1.1.12        12
  16        2         A-A-M                 1.1.1.13        13
  17        2         A-A-N                 1.1.1.14        14上面的数据实际为1是一个数据的根节点。2.3为1的子节点。4-17是2的子节点。
我想用一条SQL能够查出的数据ID顺序为： 1 2 4……17 3现在用：Select * from table as t order by t.WBS 按说应该查询需要的数据，可是因为WBS是一个varchar类型的。1-9的顺序的没错的。如果出现9以上的同级数据的WBS_TAIL顺序就会是 1，10，11，12，2，3，4，5，6，7，8，9 这样。求一条可以“先查根节点。之后根节点若存在子级。先查第一个子级。再查第一个子级的子级 。完毕后查第二个子级这样的顺序。子级10级以上，按照阿拉伯数字从小到大排序。”

Answer 1

楼主把建表语句和insert 测试数据贴出来;

Answer 2

CREATE TABLE  `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pid` int(10) unsigned NOT NULL,
  `name` varchar(45) NOT NULL,
  `wbs` varchar(45) NOT NULL,
  `wbs_tail` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
);insert into test values
(1,0,'A',1.1,1),
(2,1,'A-A','1.1.1',1),
(3,1,'A-B','1.1.2',2),
(4,2,'A-A-A','1.1.1.1',1),
(5,2,'A-A-B','1.1.1.2',2),
(6,2,'A-A-C','1.1.1.3',3),
(7,2,'A-A-D','1.1.1.4',4),
(8,2,'A-A-E','1.1.1.5',5),
(9,2,'A-A-F','1.1.1.6',6),
(10,2,'A-A-G','1.1.1.7',7),
(11,2,'A-A-H','1.1.1.8',8),
(12,2,'A-A-I','1.1.1.9',9),
(13,2,'A-A-J','1.1.1.10',10),
(14,2,'A-A-K','1.1.1.11',11),
(15,2,'A-A-L','1.1.1.12',12),
(16,2,'A-A-M','1.1.1.13',13),
(17,2,'A-A-N','1.1.1.14',14)
按照这个test data，直接order by name就可以了，除非数据量会超过Z，A-A-Z后面是A-A-AA，那就另当别论了
SELECT * FROM test order by name

Answer 3

-- 既然是wbs的缘故，那么就伪造一个排序列，将<10的加前缀0不就搞定了吗？
 select b.*
 from(
    Select case when t.wbs<10 then concat('0',t.wbs) else t.wbs end as wbs_sort, 
        t.* 
    from table as t
)b order by b.wbs_sort 

Answer 4

NAME 的数据是一些字符串。不一定是ABCD那么连续的。。

Answer 5

mysql> select * from test;
+----+-----+-------+----------+----------+
| id | pid | name  | wbs      | wbs_tail |
+----+-----+-------+----------+----------+
|  1 |   0 | A     | 1.1      |        1 |
|  2 |   1 | A-A   | 1.1.1    |        1 |
|  3 |   1 | A-B   | 1.1.2    |        2 |
|  4 |   2 | A-A-A | 1.1.1.1  |        1 |
|  5 |   2 | A-A-B | 1.1.1.2  |        2 |
|  6 |   2 | A-A-C | 1.1.1.3  |        3 |
|  7 |   2 | A-A-D | 1.1.1.4  |        4 |
|  8 |   2 | A-A-E | 1.1.1.5  |        5 |
|  9 |   2 | A-A-F | 1.1.1.6  |        6 |
| 10 |   2 | A-A-G | 1.1.1.7  |        7 |
| 11 |   2 | A-A-H | 1.1.1.8  |        8 |
| 12 |   2 | A-A-I | 1.1.1.9  |        9 |
| 13 |   2 | A-A-J | 1.1.1.10 |       10 |
| 14 |   2 | A-A-K | 1.1.1.11 |       11 |
| 15 |   2 | A-A-L | 1.1.1.12 |       12 |
| 16 |   2 | A-A-M | 1.1.1.13 |       13 |
| 17 |   2 | A-A-N | 1.1.1.14 |       14 |
+----+-----+-------+----------+----------+
17 rows in set (0.08 sec)mysql> select * from test
    -> order by SUBSTRING_INDEX(wbs, '.', 1)+0,
    ->  SUBSTRING_INDEX(SUBSTRING_INDEX(concat(wbs,'.0.0.0.0'), '.', 2),'.',-1)+0,
    ->  SUBSTRING_INDEX(SUBSTRING_INDEX(concat(wbs,'.0.0.0.0'), '.', 3),'.',-1)+0,
    ->  SUBSTRING_INDEX(SUBSTRING_INDEX(concat(wbs,'.0.0.0.0'), '.', 4),'.',-1)+0;
+----+-----+-------+----------+----------+
| id | pid | name  | wbs      | wbs_tail |
+----+-----+-------+----------+----------+
|  1 |   0 | A     | 1.1      |        1 |
|  2 |   1 | A-A   | 1.1.1    |        1 |
|  4 |   2 | A-A-A | 1.1.1.1  |        1 |
|  5 |   2 | A-A-B | 1.1.1.2  |        2 |
|  6 |   2 | A-A-C | 1.1.1.3  |        3 |
|  7 |   2 | A-A-D | 1.1.1.4  |        4 |
|  8 |   2 | A-A-E | 1.1.1.5  |        5 |
|  9 |   2 | A-A-F | 1.1.1.6  |        6 |
| 10 |   2 | A-A-G | 1.1.1.7  |        7 |
| 11 |   2 | A-A-H | 1.1.1.8  |        8 |
| 12 |   2 | A-A-I | 1.1.1.9  |        9 |
| 13 |   2 | A-A-J | 1.1.1.10 |       10 |
| 14 |   2 | A-A-K | 1.1.1.11 |       11 |
| 15 |   2 | A-A-L | 1.1.1.12 |       12 |
| 16 |   2 | A-A-M | 1.1.1.13 |       13 |
| 17 |   2 | A-A-N | 1.1.1.14 |       14 |
|  3 |   1 | A-B   | 1.1.2    |        2 |
+----+-----+-------+----------+----------+
17 rows in set (0.06 sec)mysql>

Answer 6

我想也是。
那么表结构，特别是wbs，1.1.1.1是不是必须这个格式，如果还能改动的话，有几个选择。1，如果最深N层的话（N不是特别大），建议加N个字段，order by lvl1, lvl2 ...lvlN即可2，如果层次不确定，但是某个节点的最大子节点个数确定，比如小于100个，那么就格式化wbs
1.1.1.1变成01.01.01.01