1.MySQl进行多级子查询（效果不明显，开发也不好使用）
2.MySQl进行多级子查询（适合做Excel表格等）
3.MySQl进行多级子查询（真实业务部分）
参考网址：
1.mysql中多级分类存储方式（产品分类，文章分类）：http://www.111cn.net/database/mysql/79203.htm
1.MySQl进行多级子查询
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------1.sql语句
-- Table structure for sort
-- ----------------------------
DROP TABLE IF EXISTS `sort`;
CREATE TABLE `sort` (
`id` int(10) DEFAULT NULL,
`pid` int(10) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of sort
-- ----------------------------
INSERT INTO `sort` VALUES ('1', null, 'a');
INSERT INTO `sort` VALUES ('11', '1', 'a_11');
INSERT INTO `sort` VALUES ('12', '1', 'a_12');
INSERT INTO `sort` VALUES ('13', '1', 'a_13');
INSERT INTO `sort` VALUES ('133', '13', 'a_13_3');
INSERT INTO `sort` VALUES ('134', '13', 'a-13_4');
2.指向语句
SELECT * FROM sort
WHERE
id = 1
OR pid IN (SELECT id FROM sort WHERE id = 1)
OR pid IN (
SELECT id FROM sort
WHERE pid IN (SELECT id FROM sort WHERE id = 1)
)
3.最终样式
Paste_Image.png
2.MySQl进行多级子查询（适合做Excel表格等）
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------1.sql语句
-- Table structure for sort
-- ----------------------------
DROP TABLE IF EXISTS `sort`;
CREATE TABLE `sort` (
`id` int(10) DEFAULT NULL,
`pid` int(10) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of sort
-- ----------------------------
INSERT INTO `sort` VALUES ('1', null, 'a');
INSERT INTO `sort` VALUES ('11', '1', 'a_11');
INSERT INTO `sort` VALUES ('12', '1', 'a_12');
INSERT INTO `sort` VALUES ('13', '1', 'a_13');
INSERT INTO `sort` VALUES ('133', '13', 'a_13_3');
INSERT INTO `sort` VALUES ('134', '13', 'a-13_4');
INSERT INTO `sort` VALUES ('1333', '133', 'a_13_3_3');
INSERT INTO `sort` VALUES ('1334', '133', 'a_13_3_4');
2.执行语句
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM sort AS t1
left JOIN sort AS t2 ON t2.pid = t1.id
right JOIN sort AS t3 ON t3.pid = t2.id
right JOIN sort AS t4 ON t4.pid = t3.id
where t1.name is not NULL
3.效果图
Paste_Image.png
3.MySQl进行多级子查询（真实业务部分）
1.sql部分：http://pan.baidu.com/s/1boBPEB9
2.执行语句
select
DISTINCT
menu1.m_sequence '编号',
menu1.m_name '一级目录',
menu2.m_name '二级目录',
menu3.m_name '三级目录',
IF(ISNULL(menuA.m_name),null,'√') '免费版',
IF(ISNULL(menuB.m_name),null,'√') '标准版',
IF(ISNULL(menuC.m_name),null,'√') '旗舰版'
from c_menu menu1
right JOIN c_menu AS menu2 ON menu2.m_parentSequence = menu1.m_sequence
left JOIN c_menu AS menu3 ON menu3.m_parentSequence = menu2.m_sequence
LEFT JOIN (SELECT
c_menu.m_name
FROM
c_menu
WHERE
c_menu.m_sequence
in (SELECT
c_rm.m_sequence
FROM
c_rm
WHERE
c_rm.ro_sequence in (SELECT
c_role.ro_sequence
FROM
c_role
WHERE
c_role.tempVersion = 1))) AS menuA ON
IFNULL(menu3.m_name,menu2.m_name) = menuA.m_name
LEFT JOIN (SELECT
c_menu.m_name
FROM
c_menu
WHERE
c_menu.m_sequence
in (SELECT
c_rm.m_sequence
FROM
c_rm
WHERE
c_rm.ro_sequence in (SELECT
c_role.ro_sequence
FROM
c_role
WHERE
c_role.tempVersion = 2))) AS menuB ON
IFNULL(menu3.m_name,menu2.m_name) = menuB.m_name
LEFT JOIN (SELECT
c_menu.m_name
FROM
c_menu
WHERE
c_menu.m_sequence
in (SELECT
c_rm.m_sequence
FROM
c_rm
WHERE
c_rm.ro_sequence in (SELECT
c_role.ro_sequence
FROM
c_role
WHERE
c_role.tempVersion = 3))) AS menuC ON
IFNULL(menu3.m_name,menu2.m_name) = menuC.m_name
WHERE
menu1.m_sequence
in (SELECT
c_rm.m_sequence
FROM
c_rm
WHERE
c_rm.ro_sequence in (SELECT
c_role.ro_sequence
FROM
c_role
WHERE
c_role.tempVersion IS NOT NULL))
ORDER BY
menu1.m_sequence,
menu2.m_sequence,
menu3.m_sequence
3.效果图
Paste_Image.png
