### department部门表 DDL：

CREATE TABLE `department` (
  `DEPT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DEPT_NAME` varchar(50) NOT NULL DEFAULT '',
  `DEPT_PARENT` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`DEPT_ID`)
)  DEFAULT CHARSET=gbk;

#----------------------------------------------------------------

### 初始化数据 :

INSERT INTO `department` VALUES (1, '【城南区】', 0);
INSERT INTO `department` VALUES (2, '高升桥区', 1);
INSERT INTO `department` VALUES (3, '长益1', 2);
INSERT INTO `department` VALUES (4, '长益2', 2);
INSERT INTO `department` VALUES (14, '华阳区', 1);
INSERT INTO `department` VALUES (15, '出水芙蓉1', 14);
INSERT INTO `department` VALUES (16, '出水芙蓉2', 14);
INSERT INTO `department` VALUES (87, '【城中区】', 0);
INSERT INTO `department` VALUES (88, '合江亭区', 87);
INSERT INTO `department` VALUES (89, '天仙桥1', 88);
INSERT INTO `department` VALUES (90, '天仙桥2', 88);
INSERT INTO `department` VALUES (480, '【客户服务中心】', 0);
INSERT INTO `department` VALUES (481, '售前服务组', 480);
INSERT INTO `department` VALUES (482, '售后服务部', 480);
INSERT INTO `department` VALUES (483, '市场研究部', 478);
INSERT INTO `department` VALUES (524, '【重庆分部】', 0);
INSERT INTO `department` VALUES (529, '江北区', 524);
INSERT INTO `department` VALUES (530, '沙坪坝区', 524);
INSERT INTO `department` VALUES (531, '渝北区', 524);

#----------------------------------------------------------------
取部门组织架构三级的SQL语句

select IFNULL(d.a3,IFNULL(d.a2,d.a1)) dept_id,d.dept_name
,if(SUBSTR(d.dept_name,4,1)='区','一线部门',if(left(d.dept_name,6)='【重庆分部】','重庆','二线部门')) area
from
(
    select g.DEPT_id a1,f.DEPT_id a2,s.DEPT_id a3,CONCAT(g.DEPT_NAME,'/',f.DEPT_NAME,'/',s.DEPT_NAME) dept_name
    from  department g
    left join department f on f.DEPT_PARENT=g.DEPT_id
    left join department s on s.DEPT_PARENT=f.DEPT_id
    where s.dept_id is not null and left(g.DEPT_NAME,1)='【' 
UNION ALL
    select g.DEPT_id a1,f.DEPT_id a2,null a3,CONCAT(g.DEPT_NAME,'/',f.DEPT_NAME) dept_name
    from  department g left join department f on f.DEPT_PARENT=g.DEPT_id
    where f.dept_id is not null and left(g.DEPT_NAME,1)='【' 
UNION ALL
    select g.DEPT_id a1,null a2,null a3,g.DEPT_NAME from  department g where g.dept_id is not null and left(g.DEPT_NAME,1)='【' 
    ORDER BY a1,a2,a3
) d