T_XT_JIGOUXINXI:
ID CHAR(32) ID
JIGOUMING CHAR(20) 机构名称
FUJIGOU CHAR(32) 所属机构
数据:
00000000000000000000000000000000 | 吐鲁番电业局 |
|
74850da287104c849f9233b83a2e6bff | 输电运维工区 | 00000000000000000000000000000000 |
19a1b282c84e4d0b93e45de8e217cfc2 | 变电运维工区 | 00000000000000000000000000000000 |
6ac135f8fc0a41fe896b16c3ec41330d | 变电检修工区 | 00000000000000000000000000000000 |
b12188adf66640e5ac212f51ae393db0 | 鄯善供电局 | 00000000000000000000000000000000 |
909fb66a8b1d46faaf583bdfcbc8e346 | 托克逊供电局 | 00000000000000000000000000000000 |
根据父机构查询子机构:
select t.id,rpad(' ',level*3-2,' ')||'├'||t.jigouming AS OrgName from t_xt_jigouxinxi t
start with trim(t.fujigou)IS Null /*父机构条件*/
connect by prior t.id=t.fujigou;
根据子机构查询父机构:
select t.id,rpad(' ',level*3-2,' ')||'├'||t.jigouming AS OrgName from t_xt_jigouxinxi t
start with trim(t.fujigou) IS NULL
connect by prior t.fujigou='19a1b282c84e4d0b93e45de8e217cfc2'; /*需要查询的子机构的父机构ID*/
顺便记一下Sql Server的查询写法:
表结构:
CREATE TABLE [dbo].[Organization](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrgName] [nchar](30) NULL,
[PrivOrgID] [int] NULL,
CONSTRAINT [PK_Organization] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
select (case id when null then '' else space(id) end ) +OrgName as OrgName from Organization