无限级分类并按层级排序分页

最新推荐文章于 2024-08-22 20:13:48 发布

原创最新推荐文章于 2024-08-22 20:13:48 发布 · 1.7k 阅读

CC 4.0 BY-SA版权

本文探讨了如何通过SQL查询优化，实现复杂层级数据的有序展示，特别是在处理多级分类时，通过对orderby字段的巧妙运用，确保数据按任意级别进行正确排序。文章提供了具体的SQL语句示例，展示了如何为每一项数据计算新的排序依据，从而实现在创建或修改项时的智能排序。

存在问题：

怎么解决最下面表格橙色行的排序跟任何级别都可以orderby字段排序，或者在创建或修改项时能先计算排序？

-- 0的长度建议是字段id的长度
SELECT id, path, title, orderby, CONCAT('1', RIGHT( CONCAT(  '00000000000', IF( toppid, toppid, id ) ) , 11 ) , REPLACE( path,  ',',  '' ) , id,  '' ) AS neworderby
FROM  `categorys` 
WHERE 1 
ORDER BY IF( toppid, toppid, orderby ) ASC , IF( toppid, toppid, id ) DESC , CONCAT('1',  RIGHT( CONCAT(  '00000000000', IF( toppid, toppid, id ) ) , 11 ) , REPLACE( path,  ',',  '' ) , id,  '' ) 
LIMIT 0 , 30

-- 测试数据
DROP TABLE IF EXISTS `categorys`;
CREATE TABLE IF NOT EXISTS `categorys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` tinyint(3) NOT NULL DEFAULT '0' COMMENT '分类',
  `pid` int(11) NOT NULL DEFAULT '0' COMMENT '上级',
  `toppid` int(11) NOT NULL DEFAULT '0',
  `path` varchar(2048) NOT NULL COMMENT '路径',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `pyfirst` varchar(25) NOT NULL COMMENT '首字母',
  `pyall` varchar(255) NOT NULL COMMENT '全拼',
  `thumb` varchar(255) NOT NULL COMMENT '略缩图',
  `recmd` tinyint(1) NOT NULL DEFAULT '0' COMMENT '推荐',
  `orderby` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0隐藏1显示',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

INSERT INTO `categorys` (`id`, `type`, `pid`, `toppid`, `path`, `title`, `pyfirst`, `pyall`, `thumb`, `recmd`, `orderby`, `status`) VALUES
(1, 2, 0, 0, '', '美食', 'm s', 'mei shi', '', 0, 0, 0),
(2, 2, 1, 1, '1', '中餐', 'z c', 'zhong can', '', 0, 0, 0),
(3, 2, 2, 1, '1,2', '早餐', 'z c', 'zao can', '', 0, 0, 0),
(4, 2, 1, 1, '1', 'dfsd', 'd', 'dfsd', '', 0, 0, 0),
(5, 2, 4, 1, '1,4', 'fdsfsd', 'f', 'fdsfsd', '', 0, 0, 0),
(6, 2, 2, 1, '1,2', 'fsdfsd', 'f', 'fsdfsd', '', 0, 0, 0),
(7, 2, 1, 1, '1', 'fdsfsd', 'f', 'fdsfsd', '', 0, 1, 0),
(8, 2, 1, 1, '1', 'dfsdf', 'd', 'dfsdf', '', 0, 0, 0),
(9, 2, 7, 1, '1,7', 'fdsfs', 'f', 'fdsfs', '', 0, 0, 0),
(10, 2, 5, 1, '1,4,5', 'fsdf', 'f', 'fsdf', '', 0, 0, 0),
(11, 2, 4, 1, '1,4', 'fsdf', 'f', 'fsdf', '', 0, 0, 0),
(12, 2, 8, 1, '1,8', 'fsdfsd', 'f', 'fsdfsd', '', 0, 0, 0),
(13, 2, 1, 1, '1', '测试', 'c s', 'ce shi', '', 0, 0, 0),
(14, 2, 0, 0, '', '酒店', 'j d', 'jiu dian', '', 0, 2, 0),
(15, 2, 14, 14, '14', '休闲', 'x x', 'xiu xian', '', 0, 0, 0);

id	path路径	orderby只支持一级排序	neworderby
14		1	10000000001414
15	14	0	1000000000141415
1		2	1000000000011
13	1	0	100000000001113
2	1	0	10000000000112
3	1,2	0	100000000001123
6	1,2	0	100000000001126
4	1	0	10000000000114
11	1,4	0	1000000000011411
5	1,4	0	100000000001145
10	1,4,5	0	10000000000114510
7	1	1	10000000000117
9	1,7	0	100000000001179
8	1	0	10000000000118
12	1,8	0	1000000000011812