网页产生二级菜单的SQL优化

将菜单放一张表中,查询时通过用户id查到组id,然后再用组id查权限表,获得有权限的菜单id。
最后组装成两级菜单的数据。
数据列为菜单名,菜单链接,菜单级别,父菜单id,该级菜单的排序。sql如下:

CREATE TABLE `column` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `code` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `sort_no` int(11) DEFAULT NULL,
  `tree_path` varchar(300) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO `column` VALUES ('1', '网络运营', 'p0', '0', '1', null);
INSERT INTO `column` VALUES ('2', '系统管理', 'p9', '0', '9', null);
INSERT INTO `column` VALUES ('3', '开账户', 'p4', '1', '1', null);
INSERT INTO `column` VALUES ('4', '开业务', 'p66', '1', '2', null);
INSERT INTO `column` VALUES ('5', '财务', 'p2', '0', '2', null);
INSERT INTO `column` VALUES ('6', '用户查业务', 'p33', '5', '1', null);
INSERT INTO `column` VALUES ('7', '业务查用户', 'p55', '5', '2', null);
INSERT INTO `column` VALUES ('8', '查缴费', 'p99', '5', '3', null);
INSERT INTO `column` VALUES ('9', '客服', 'p44', '0', '4', null);

最初想是将表关联然后查找,sql如下:

select xc1.id as id1, xc1.name as name1, xc1.code as code1, xc1.parent_id as pid1, 
xc2.name as name2,xc2.code as code2 from column as xc1 
LEFT JOIN column as xc2 on xc2.parent_id = xc1.id 
where xc1.id in ( 
  select column_id from group_column as gr where group_id in( 
   select group_id from group_user where user_id=1
  ) 
) and xc2.id in ( 
  select column_id from group_column as gr where group_id in( 
   select group_id from group_user where user_id=1
  )  
) 
and xc1.parent_id=0 ORDER BY xc1.parent_id,xc1.sort_no,xc2.sort_no

这个sql有个缺点,一比较长,二获得权限部分写了两次。然后换个写法,先把有权限的选出然后再关联。

select xc2.id as id1, xc2.name as name1, xc2.code as code1, xc2.parent_id as pid1, 
  xc1.name as name2, xc1.code as code2 from (
	 select id,name,code,parent_id,sort_no from column where id in ( 
		select column_id from group_column as gr where group_id in( 
		  select group_id from group_user where user_id=1
		 ) 
    ) and parent_id <>0 ORDER BY parent_id,sort_no 
) as xc1
left join column as xc2 on xc1.parent_id=xc2.id 

因为只有10条数据,所以通过多次手动执行也没有发现性能差多少,但是短了不少。

发表评论

电子邮件地址不会被公开。 必填项已用*标注