mysql - 按路径和排序顺序获取类别树

Question

我的类别结构如下所示。我的目的是通过单个查询获取类别结构。我按路径对表进行排序，然后得到类别树。

sort_order : 表示同级别类别之间的排序顺序

当我运行查询时

SELECT * FROM category order by path;

结果:

但结果必须如下图所示因为Electronic类的sort_order为1，小于Computer类2的sort_order值

 - ELectronic 
 - - TV 
 - - - LCD 
 - - - - LED LCD 
 - Computer 
 - - Laptop

Answer 1

EDIT 添加了 SQL Fiddle 东西

SQL Fiddle

MySQL 5.5.28 架构设置:

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `label` varchar(20) NOT NULL,
  `level` int(11) NOT NULL,
  `path` varchar(100) NOT NULL,
  `sortorder` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`id`, `parent_id`, `label`, `level`, `path`, `sortorder`) VALUES
(0, 0, '', 0, '', 0),
(2, 0, 'Computer', 1, '.2.', 2),
(3, 0, 'Electronic', 1, '.3.', 1),
(4, 3, 'TV', 2, '.3.4.', 3),
(5, 2, 'Laptop', 2, '.2.5.', 4),
(6, 7, 'LED LCD', 4, '.3.4.7.6.', 5),
(7, 4, 'LCD', 3, '.3.4.7', 5);

查询 1:

SELECT 
  (
    SELECT group_concat( cast( g.sortorder AS char ) ORDER BY g.path ) AS gso
    FROM category AS g
    WHERE c.path LIKE concat( g.path, '%' )
  ) AS grsortorder, c. *
FROM `category` AS c
ORDER BY grsortorder

Results :

| GRSORTORDER | ID | PARENT_ID |      LABEL | LEVEL |      PATH | SORTORDER |
-----------------------------------------------------------------------------
|           0 |  0 |         0 |            |     0 |           |         0 |
|         0,1 |  3 |         0 | Electronic |     1 |       .3. |         1 |
|       0,1,3 |  4 |         3 |         TV |     2 |     .3.4. |         3 |
|     0,1,3,5 |  7 |         4 |        LCD |     3 |    .3.4.7 |         5 |
|   0,1,3,5,5 |  6 |         7 |    LED LCD |     4 | .3.4.7.6. |         5 |
|         0,2 |  2 |         0 |   Computer |     1 |       .2. |         2 |
|       0,2,4 |  5 |         2 |     Laptop |     2 |     .2.5. |         4 |