表user:userid,username,depid(所属部门id,对应就是表department的id字段).
表department(部门):id,nodename,parent(父结点id)
表department表是一个树结构.
我想取得userid,username,depid(所属部门id),depname(所属部门名),parentid(父部门id),parentname(父部门名).
能用一条sql语句来写吗?
表department(部门):id,nodename,parent(父结点id)
表department表是一个树结构.
我想取得userid,username,depid(所属部门id),depname(所属部门名),parentid(父部门id),parentname(父部门名).
能用一条sql语句来写吗?
Organization :PKID Name ParentID//对应你的department表select PKID,Name,OrgID,(select Name from organization o where o.PKID=OrgID) OrgName,(select ParentID from organization o where o.PKID=OrgID) ParentID,(select Name from organization o where o.PKID=(select ParentID from organization o where o.PKID=OrgID)) ParenName from Users
select UserID,UserName,depID,(select nodeName from department o where o.id=depID) OrgName,(select Parent from department o where o.ID=depID) ParentID,(select nodeName from department o where o.ID=(select Parent from department o where o.ID=depID)) ParenName from User
你试试
建议不要用关键字作表名
thx,学到。
没想到能这么写。To:silverdrag(水月·生)
我没有parent表,parent表可能要紧跟一个查询语句吧。我自己写的如下。
select E_User.UserId,E_Department.nodename as depName,E_Department.id as depId,E_Department.parent as ParentId,e.nodename as parentName from E_User
Left Join E_Department On E_User.DepId = E_Department.id
Left Join E_Department e On E_Department.parent = e.id
[user].userid,
[user].username,
[user].depid,
[department].nodename AS deptname,
department_parent.nodename AS parentname
FROM
[user] INNER JOIN
[department] ON department.id = [user].depid INNER JOIN
[department] department_parent ON department_parent.id = [department].id