Mysql刷题笔记 12.30 根据对应id构建信息表

-- 12.30 根据对应id构建信息表
CREATE TABLE T1230 
(
  ID INT,
  Cname VARCHAR(20),
  ParentID INT,
  OrgID INT,
  Type INT
)

INSERT INTO T1230 VALUES (1,'公司总部',0,0,1);
INSERT INTO T1230 VALUES (2,'人事部',1,1,2);
INSERT INTO T1230 VALUES (3,'张三',2,1,3);
INSERT INTO T1230 VALUES (4,'财物部',1,1,2);
INSERT INTO T1230 VALUES (5,'李四',4,1,3);

T1230
+------+--------------+----------+-------+------+
| ID   | Cname        | ParentID | OrgID | Type |
+------+--------------+----------+-------+------+
|    1 | 公司总部     |        0 |     0 |    1 |
|    2 | 人事部       |        1 |     1 |    2 |
|    3 | 张三         |        2 |     1 |    3 |
|    4 | 财物部       |        1 |     1 |    2 |
|    5 | 李四         |        4 |     1 |    3 |
+------+--------------+----------+-------+------+

结果
+------+--------+--------------+-----------+
| ID   | Cname  | OrgName      | DeptName  |
+------+--------+--------------+-----------+
|    3 | 张三   | 公司总部     	 | 人事部    |
|    5 | 李四   | 公司总部       | 财物部    |
+------+--------+--------------+-----------+

SELECT a.ID,a.Cname,b.Cname as OrgName,c.Cname as DeptName
FROM T1230 a
JOIN T1230 b ON a.OrgID = b.ID
JOIN T1230 c ON a.ParentID = c.ID
WHERE a.Type=3