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

Question

我使用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语句实现，复杂点、效率低点都无所谓了。  
 
请高手帮忙了！

Answer 1

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

Answer 2

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

Answer 3

搞定!不过还真是麻烦!
看语句:
(
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研发中心 
总经理办公室 工程部       工程部       工程部 
总经理办公室 工程部       实施中心     实施中心 
总经理办公室 工程部       设备中心     设备中心 
总经理办公室 业务部       业务部       业务部 取最后的一列就可以了!

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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研发中心  
工程部  
实施中心  
设备中心  
业务部  

Answer 11

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

Answer 12

其实改也不会有麻烦
你只要改完部门之后
把这条记录的zhid=它的父结点zhid+自己的id
就可以了如果数据库里不保存所有相关ID
肯定在代码里要去做循环之类
写个方法动态得到zhid这样修改部门就不会麻烦了
getzhid(id)
(
)
然后
select name from dept order by getzhid(id)

Answer 13

用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研发中心
工程部
实施中心
设备中心
业务部

Answer 14

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');  
--------------------------------------------------------------------------------
这种方法不好,如果要删除一个子树和修改其节点编号就麻烦了