100分求一个简单的sql，请高手帮忙，2天内结贴！

[wxt1013]

我使用mysql数据库，有一个部门表dept，表结构如下：

CREATE TABLE `dept` (
`id` varchar(255) NOT NULL, --主编号
`name` varchar(255) default NULL, --部门名称
`parentId` varchar(255) default NULL, --本部门的上级部门
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `dept` VALUES ('0001', '总经理办公室', '');
INSERT INTO `dept` VALUES ('0002', '技术部', '0001');
INSERT INTO `dept` VALUES ('0003', '工程部', '0001');
INSERT INTO `dept` VALUES ('0004', '业务部', '0001');
INSERT INTO `dept` VALUES ('0005', 'OA研发中心', '0002');
INSERT INTO `dept` VALUES ('0006', 'CRM研发中心', '0002');
INSERT INTO `dept` VALUES ('0007', '公文研发组', '0005');
INSERT INTO `dept` VALUES ('0008', '实施中心', '0003');
INSERT INTO `dept` VALUES ('0009', '设备中心', '0003');

写个sql语句，将所有部门分级显示出来，
如，当前数据应该这样显示：

总经理办公室
----技术部
--------OA研发中心
------------公文研发组
--------CRM研发中心
----工程部
--------实施中心
--------设备中心
----业务部

或者数据这样显示也可以，我自己程序处理一下即可：

总经理办公室
技术部
OA研发中心
公文研发组
CRM研发中心
工程部
实施中心
设备中心
业务部

注：尽量不要使用数据库自带的函数，尽量使用SQL语句实现，复杂点、效率低点都无所谓了。

请高手帮忙了！

[yifuzhiming]

我不是高手,只能帮你顶了

[wxt1013]

自己顶一下吧

[SYUEHOKO]

你把每个部门都用一个固定的ID号INSERT进数据库.. 然后进行排序即可.

[wxt1013]

以前我用Hibernate，通过自身one-to-many的级联实现了这个功能，但用SQL或存储过程怎样实现，我却无从下手，工作3年以来，所有的项目都采用Hibernate作为持久层工具，因此SQL编写能力很弱，特此下帖，望高人指点。

[Coder1035]

to：ldq210()
谢谢你的真诚！
对于部门，子部门的深度是未知的，可能是10层也可能是20层，你给出的答案，是针对我设定的测试数据的，能否找到一个通用的SQL语句，或者存储过程来解决这个问题？


如果是这样子，建议你修改表结构！！！！

[Coder1035]

或者: 使用Treeview来表现，一次只获取当前节点的下一层数据！

[wxt1013]

to：lchzh(随风)
能否指点一下，如何修改表结构？

[giftzheng]

CREATE TABLE `dept` (
`id` varchar(255) NOT NULL, --主编号
`name` varchar(255) default NULL, --部门名称
`parentId` varchar(255) default NULL, --本部门的上级部门
zhid varchar(500) ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `dept` VALUES ('0001', '总经理办公室', '','');
INSERT INTO `dept` VALUES ('0002', '技术部', '0001','00010002');
INSERT INTO `dept` VALUES ('0003', '工程部', '0001',00010003);
INSERT INTO `dept` VALUES ('0004', '业务部', '0001','00010004');
INSERT INTO `dept` VALUES ('0005', 'OA研发中心', '0002','000100020005');
INSERT INTO `dept` VALUES ('0006', 'CRM研发中心', '0002','000100020006');
INSERT INTO `dept` VALUES ('0007', '公文研发组', '0005','0001000200050007');
INSERT INTO `dept` VALUES ('0008', '实施中心', '0003','000100030008');
INSERT INTO `dept` VALUES ('0009', '设备中心', '0003','000100030009');
--没弄过mysql你自己改改 加字段zhid保存所有上级结点id+本身ID
select name from dept order by zhid
就得到
总经理办公室
技术部
OA研发中心
公文研发组
CRM研发中心
工程部
实施中心
设备中心
业务部

[wxt1013]

to：giftzheng
这种解决方案我也采用过，他的有点就是检索时比较方案，但缺点也很明显，修改某个部门的上级部门时麻烦较大，如果控制不好有可能出现死循环，或者将“zhid”字段搞混乱。
如果使用Hibernate，我个人认为我帖子上的设计比较合理，但采用JDBC，检索数据时较麻烦。

[giftzheng]

其实改也不会有麻烦
你只要改完部门之后
把这条记录的zhid=它的父结点zhid+自己的id
就可以了

如果数据库里不保存所有相关ID
肯定在代码里要去做循环之类
写个方法动态得到zhid这样修改部门就不会麻烦了
getzhid(id)
(
)
然后
select name from dept order by getzhid(id)

[cyb1017]

用SQLServer可以实现,有价值就参考一下吧

--删除可能已经存在的表和函数
drop function f_go
drop table dept

--建立数据表
create table dept
(id nvarchar(20),DeptName nvarchar(20),parentId nvarchar(20))

INSERT INTO dept VALUES ('0001', '总经理办公室', '');
INSERT INTO dept VALUES ('0002', '技术部', '0001');
INSERT INTO dept VALUES ('0003', '工程部', '0001');
INSERT INTO dept VALUES ('0004', '业务部', '0001');
INSERT INTO dept VALUES ('0005', 'OA研发中心', '0002');
INSERT INTO dept VALUES ('0006', 'CRM研发中心', '0002');
INSERT INTO dept VALUES ('0007', '公文研发组', '0005');
INSERT INTO dept VALUES ('0008', '实施中心', '0003');
INSERT INTO dept VALUES ('0009', '设备中心', '0003');
go

--建立自定义函数
create function f_go(@col nvarchar)
returns @Temp table(id nvarchar(20),DeptName nvarchar(20), parentId nvarchar(20),Deptlevel int,Position nvarchar(200) )
as

begin
declare @Level int
set @Level=1
insert @Temp select *,@Level,'' from dept where parentid=@col
while @@rowcount<>0
begin
set @Level=@Level+1
insert @Temp select a.*, @Level, b.Position+a.id from dept a,@Temp b where a.parentId=b.id and b.Deptlevel=@Level-1
end
return
end

go

--执行查询
select DeptName from dbo.f_go(N'') order by Position

--删除上面建立的表和函数
drop function f_go
drop table dept
go


结果是：
总经理办公室
技术部
OA研发中心
公文研发组
CRM研发中心
工程部
实施中心
设备中心
业务部

[商科程序员]

CREATE TABLE `dept` (
`id` varchar(255) NOT NULL, --主编号
`name` varchar(255) default NULL, --部门名称
`parentId` varchar(255) default NULL, --本部门的上级部门
zhid varchar(500) ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `dept` VALUES ('0001', '总经理办公室', '','');
INSERT INTO `dept` VALUES ('0002', '技术部', '0001','00010002');
INSERT INTO `dept` VALUES ('0003', '工程部', '0001',00010003);
INSERT INTO `dept` VALUES ('0004', '业务部', '0001','00010004');
INSERT INTO `dept` VALUES ('0005', 'OA研发中心', '0002','000100020005');
INSERT INTO `dept` VALUES ('0006', 'CRM研发中心', '0002','000100020006');
INSERT INTO `dept` VALUES ('0007', '公文研发组', '0005','0001000200050007');
INSERT INTO `dept` VALUES ('0008', '实施中心', '0003','000100030008');
INSERT INTO `dept` VALUES ('0009', '设备中心', '0003','000100030009');
--------------------------------------------------------------------------------
这种方法不好,如果要删除一个子树和修改其节点编号就麻烦了

[wxt1013]

to：SYUEHOKO()
这种办法我想过，但在修改数据的时候，会出现较大的麻烦，所以采用了parentId作为上下级的连接点。

[ldq210]

搞定!不过还真是麻烦!
看语句:
(
SELECT M.name AS m_name, D.name AS d_name, P.name AS p_name, P.name AS l_name
FROM dept D
LEFT JOIN dept M ON D.parentId = M.id
LEFT JOIN dept P ON P.parentId = D.id
WHERE M.name = '总经理办公室' AND P.name != 'null'
)
UNION (

SELECT M.name AS m_name, D.name AS d_name, P.name AS p_name, L.name AS l_name
FROM dept D
LEFT JOIN dept M ON D.parentId = M.id
LEFT JOIN dept P ON P.parentId = D.id
LEFT JOIN dept L ON L.parentID = P.id
WHERE M.name != 'null' AND L.name != 'null'
)
UNION (

SELECT M.name AS m_name, D.name AS d_name, D.name AS p_name, D.name AS l_name
FROM dept D
LEFT JOIN dept M ON D.parentId = M.id
WHERE M.name != 'null' AND M.name = '总经理办公室'
)
UNION (

SELECT D.name AS m_name, D.name AS d_name, D.name AS p_name, D.name AS l_name
FROM dept D
WHERE D.name = '总经理办公室'
)
ORDER BY d_name DESC , p_name DESC


执行的结果是:
m_name d_name p_name l_name
总经理办公室 总经理办公室 总经理办公室 总经理办公室
总经理办公室 技术部 技术部 技术部
总经理办公室 技术部 OA研发中心 OA研发中心
总经理办公室 技术部 OA研发中心 公文研发组
总经理办公室 技术部 CRM研发中心 CRM研发中心
总经理办公室 工程部 工程部 工程部
总经理办公室 工程部 实施中心 实施中心
总经理办公室 工程部 设备中心 设备中心
总经理办公室 业务部 业务部 业务部

取最后的一列就可以了!

[wxt1013]

to：ldq210()
谢谢你的真诚！
对于部门，子部门的深度是未知的，可能是10层也可能是20层，你给出的答案，是针对我设定的测试数据的，能否找到一个通用的SQL语句，或者存储过程来解决这个问题？

[ldq210]

我没测试过!应该是不行的!我觉得要是想要写成通用的,可以把它做为一个专门的课题来研究了.我水平有限,不知道哪为高手还有更好、通用的SQL.发上来！大家研究研究！

[cyb1017]

增加额外字段的方法不好,链表结构做成这样是迁就程序员,丢掉了结构具备的优点.

[yqwaxyq]

用递归能解决

[wxt1013]

明天放假，今天就结贴吧。谢谢各位的鼎力相助！