56,677
社区成员
发帖
与我相关
我的任务
分享
### 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