MySQL树形遍历

原文出自http://blog.itpub.net/29254281/viewspace-1851457/

借鉴Oracle Hr模式下的Employees表的结构和数据

  1. DROP TABLE IF EXISTS `employees`;
  2. CREATE TABLE `employees` (
  3.   `employee_id` int(11) NOT NULL,
  4.   `FIRST_NAME` varchar(20) DEFAULT NULL,
  5.   `LAST_NAME` varchar(25) DEFAULT NULL,
  6.   `EMAIL` varchar(25) DEFAULT NULL,
  7.   `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  8.   `HIRE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  9.   `JOB_ID` varchar(10) DEFAULT NULL,
  10.   `SALARY` int(11) DEFAULT NULL,
  11.   `commission_pct` float DEFAULT NULL,
  12.   `manager_id` int(11) DEFAULT NULL,
  13.   `department_id` int(11) DEFAULT NULL,
  14.   PRIMARY KEY (`employee_id`)
  15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  16. INSERT INTO `employees` VALUES (100,'Steven','King','SKING','515.123.4567','2015-12-02 03:21:38','AD_PRES',24000,NULL,NULL,90);
  17. INSERT INTO `employees` VALUES (101,'Neena','Kochhar','NKOCHHAR','515.123.4568','2015-12-02 03:21:38','AD_VP',17000,NULL,100,90);
  18. INSERT INTO `employees` VALUES (102,'Lex','De Haan','LDEHAAN','515.123.4569','2015-12-02 03:21:38','AD_VP',17000,NULL,100,90);
  19. INSERT INTO `employees` VALUES (103,'Alexander','Hunold','AHUNOLD','590.423.4567','2015-12-02 03:21:38','IT_PROG',9000,NULL,102,60);
  20. INSERT INTO `employees` VALUES (104,'Bruce','Ernst','BERNST','590.423.4568','2015-12-02 03:21:38','IT_PROG',6000,NULL,103,60);
  21. INSERT INTO `employees` VALUES (105,'David','Austin','DAUSTIN','590.423.4569','2015-12-02 03:21:38','IT_PROG',4800,NULL,103,60);
  22. INSERT INTO `employees` VALUES (106,'Valli','Pataballa','VPATABAL','590.423.4560','2015-12-02 03:21:38','IT_PROG',4800,NULL,103,60);
  23. INSERT INTO `employees` VALUES (107,'Diana','Lorentz','DLORENTZ','590.423.5567','2015-12-02 03:21:38','IT_PROG',4200,NULL,103,60);
  24. INSERT INTO `employees` VALUES (108,'Nancy','Greenberg','NGREENBE','515.124.4569','2015-12-02 03:21:38','FI_MGR',12008,NULL,101,100);
  25. INSERT INTO `employees` VALUES (109,'Daniel','Faviet','DFAVIET','515.124.4169','2015-12-02 03:21:38','FI_ACCOUNT',9000,NULL,108,100);
  26. INSERT INTO `employees` VALUES (110,'John','Chen','JCHEN','515.124.4269','2015-12-02 03:21:38','FI_ACCOUNT',8200,NULL,108,100);
  27. INSERT INTO `employees` VALUES (111,'Ismael','Sciarra','ISCIARRA','515.124.4369','2015-12-02 03:21:38','FI_ACCOUNT',7700,NULL,108,100);
  28. INSERT INTO `employees` VALUES (112,'Jose Manuel','Urman','JMURMAN','515.124.4469','2015-12-02 03:21:38','FI_ACCOUNT',7800,NULL,108,100);
  29. INSERT INTO `employees` VALUES (113,'Luis','Popp','LPOPP','515.124.4567','2015-12-02 03:21:38','FI_ACCOUNT',6900,NULL,108,100);
  30. INSERT INTO `employees` VALUES (114,'Den','Raphaely','DRAPHEAL','515.127.4561','2015-12-02 03:21:38','PU_MAN',11000,NULL,100,30);
  31. INSERT INTO `employees` VALUES (115,'Alexander','Khoo','AKHOO','515.127.4562','2015-12-02 03:21:38','PU_CLERK',3100,NULL,114,30);
  32. INSERT INTO `employees` VALUES (116,'Shelli','Baida','SBAIDA','515.127.4563','2015-12-02 03:21:38','PU_CLERK',2900,NULL,114,30);
  33. INSERT INTO `employees` VALUES (117,'Sigal','Tobias','STOBIAS','515.127.4564','2015-12-02 03:21:38','PU_CLERK',2800,NULL,114,30);
  34. INSERT INTO `employees` VALUES (118,'Guy','Himuro','GHIMURO','515.127.4565','2015-12-02 03:21:38','PU_CLERK',2600,NULL,114,30);
  35. INSERT INTO `employees` VALUES (119,'Karen','Colmenares','KCOLMENA','515.127.4566','2015-12-02 03:21:38','PU_CLERK',2500,NULL,114,30);
  36. INSERT INTO `employees` VALUES (120,'Matthew','Weiss','MWEISS','650.123.1234','2015-12-02 03:21:38','ST_MAN',8000,NULL,100,50);
  37. INSERT INTO `employees` VALUES (121,'Adam','Fripp','AFRIPP','650.123.2234','2015-12-02 03:21:38','ST_MAN',8200,NULL,100,50);
  38. INSERT INTO `employees` VALUES (122,'Payam','Kaufling','PKAUFLIN','650.123.3234','2015-12-02 03:21:38','ST_MAN',7900,NULL,100,50);
  39. INSERT INTO `employees` VALUES (123,'Shanta','Vollman','SVOLLMAN','650.123.4234','2015-12-02 03:21:38','ST_MAN',6500,NULL,100,50);
  40. INSERT INTO `employees` VALUES (124,'Kevin','Mourgos','KMOURGOS','650.123.5234','2015-12-02 03:21:38','ST_MAN',5800,NULL,100,50);
  41. INSERT INTO `employees` VALUES (125,'Julia','Nayer','JNAYER','650.124.1214','2015-12-02 03:21:38','ST_CLERK',3200,NULL,120,50);
  42. INSERT INTO `employees` VALUES (126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','2015-12-02 03:21:38','ST_CLERK',2700,NULL,120,50);
  43. INSERT INTO `employees` VALUES (127,'James','Landry','JLANDRY','650.124.1334','2015-12-02 03:21:38','ST_CLERK',2400,NULL,120,50);
  44. INSERT INTO `employees` VALUES (128,'Steven','Markle','SMARKLE','650.124.1434','2015-12-02 03:21:38','ST_CLERK',2200,NULL,120,50);
  45. INSERT INTO `employees` VALUES (129,'Laura','Bissot','LBISSOT','650.124.5234','2015-12-02 03:21:38','ST_CLERK',3300,NULL,121,50);
  46. INSERT INTO `employees` VALUES (130,'Mozhe','Atkinson','MATKINSO','650.124.6234','2015-12-02 03:21:38','ST_CLERK',2800,NULL,121,50);
  47. INSERT INTO `employees` VALUES (131,'James','Marlow','JAMRLOW','650.124.7234','2015-12-02 03:21:38','ST_CLERK',2500,NULL,121,50);
  48. INSERT INTO `employees` VALUES (132,'TJ','Olson','TJOLSON','650.124.8234','2015-12-02 03:21:38','ST_CLERK',2100,NULL,121,50);
  49. INSERT INTO `employees` VALUES (133,'Jason','Mallin','JMALLIN','650.127.1934','2015-12-02 03:21:38','ST_CLERK',3300,NULL,122,50);
  50. INSERT INTO `employees` VALUES (134,'Michael','Rogers','MROGERS','650.127.1834','2015-12-02 03:21:38','ST_CLERK',2900,NULL,122,50);
  51. INSERT INTO `employees` VALUES (135,'Ki','Gee','KGEE','650.127.1734','2015-12-02 03:21:38','ST_CLERK',2400,NULL,122,50);
  52. INSERT INTO `employees` VALUES (136,'Hazel','Philtanker','HPHILTAN','650.127.1634','2015-12-02 03:21:38','ST_CLERK',2200,NULL,122,50);
  53. INSERT INTO `employees` VALUES (137,'Renske','Ladwig','RLADWIG','650.121.1234','2015-12-02 03:21:38','ST_CLERK',3600,NULL,123,50);
  54. INSERT INTO `employees` VALUES (138,'Stephen','Stiles','SSTILES','650.121.2034','2015-12-02 03:21:38','ST_CLERK',3200,NULL,123,50);
  55. INSERT INTO `employees` VALUES (139,'John','Seo','JSEO','650.121.2019','2015-12-02 03:21:38','ST_CLERK',2700,NULL,123,50);
  56. INSERT INTO `employees` VALUES (140,'Joshua','Patel','JPATEL','650.121.1834','2015-12-02 03:21:38','ST_CLERK',2500,NULL,123,50);
  57. INSERT INTO `employees` VALUES (141,'Trenna','Rajs','TRAJS','650.121.8009','2015-12-02 03:21:38','ST_CLERK',3500,NULL,124,50);
  58. INSERT INTO `employees` VALUES (142,'Curtis','Davies','CDAVIES','650.121.2994','2015-12-02 03:21:38','ST_CLERK',3100,NULL,124,50);
  59. INSERT INTO `employees` VALUES (143,'Randall','Matos','RMATOS','650.121.2874','2015-12-02 03:21:38','ST_CLERK',2600,NULL,124,50);
  60. INSERT INTO `employees` VALUES (144,'Peter','Vargas','PVARGAS','650.121.2004','2015-12-02 03:21:38','ST_CLERK',2500,NULL,124,50);
  61. INSERT INTO `employees` VALUES (145,'John','Russell','JRUSSEL','011.44.1344.429268','2004-09-30 16:00:00','SA_MAN',14000,0.4,100,80);
  62. INSERT INTO `employees` VALUES (146,'Karen','Partners','KPARTNER','011.44.1344.467268','2005-01-04 16:00:00','SA_MAN',13500,0.3,100,80);
  63. INSERT INTO `employees` VALUES (147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','2005-03-09 16:00:00','SA_MAN',12000,0.3,100,80);
  64. INSERT INTO `employees` VALUES (148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','2007-10-14 16:00:00','SA_MAN',11000,0.3,100,80);
  65. INSERT INTO `employees` VALUES (149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','2008-01-28 16:00:00','SA_MAN',10500,0.2,100,80);
  66. INSERT INTO `employees` VALUES (150,'Peter','Tucker','PTUCKER','011.44.1344.129268','2005-01-29 16:00:00','SA_REP',10000,0.3,145,80);
  67. INSERT INTO `employees` VALUES (151,'David','Bernstein','DBERNSTE','011.44.1344.345268','2005-03-23 16:00:00','SA_REP',9500,0.25,145,80);
  68. INSERT INTO `employees` VALUES (152,'Peter','Hall','PHALL','011.44.1344.478968','2005-08-19 16:00:00','SA_REP',9000,0.25,145,80);
  69. INSERT INTO `employees` VALUES (153,'Christopher','Olsen','COLSEN','011.44.1344.498718','2006-03-29 16:00:00','SA_REP',8000,0.2,145,80);
  70. INSERT INTO `employees` VALUES (154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','2006-12-08 16:00:00','SA_REP',7500,0.2,145,80);
  71. INSERT INTO `employees` VALUES (155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','2007-11-22 16:00:00','SA_REP',7000,0.15,145,80);
  72. INSERT INTO `employees` VALUES (156,'Janette','King','JKING','011.44.1345.429268','2004-01-29 16:00:00','SA_REP',10000,0.35,146,80);
  73. INSERT INTO `employees` VALUES (157,'Patrick','Sully','PSULLY','011.44.1345.929268','2004-03-03 16:00:00','SA_REP',9500,0.35,146,80);
  74. INSERT INTO `employees` VALUES (158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','2004-07-31 16:00:00','SA_REP',9000,0.35,146,80);
  75. INSERT INTO `employees` VALUES (159,'Lindsey','Smith','LSMITH','011.44.1345.729268','2005-03-09 16:00:00','SA_REP',8000,0.3,146,80);
  76. INSERT INTO `employees` VALUES (160,'Louise','Doran','LDORAN','011.44.1345.629268','2005-12-14 16:00:00','SA_REP',7500,0.3,146,80);
  77. INSERT INTO `employees` VALUES (161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','2006-11-02 16:00:00','SA_REP',7000,0.25,146,80);
  78. INSERT INTO `employees` VALUES (162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','2005-11-10 16:00:00','SA_REP',10500,0.25,147,80);
  79. INSERT INTO `employees` VALUES (163,'Danielle','Greene','DGREENE','011.44.1346.229268','2007-03-18 16:00:00','SA_REP',9500,0.15,147,80);
  80. INSERT INTO `employees` VALUES (164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','2008-01-23 16:00:00','SA_REP',7200,0.1,147,80);
  81. INSERT INTO `employees` VALUES (165,'David','Lee','DLEE','011.44.1346.529268','2008-02-22 16:00:00','SA_REP',6800,0.1,147,80);
  82. INSERT INTO `employees` VALUES (166,'Sundar','Ande','SANDE','011.44.1346.629268','2008-03-23 16:00:00','SA_REP',6400,0.1,147,80);
  83. INSERT INTO `employees` VALUES (167,'Amit','Banda','ABANDA','011.44.1346.729268','2008-04-20 16:00:00','SA_REP',6200,0.1,147,80);
  84. INSERT INTO `employees` VALUES (168,'Lisa','Ozer','LOZER','011.44.1343.929268','2005-03-10 16:00:00','SA_REP',11500,0.25,148,80);
  85. INSERT INTO `employees` VALUES (169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','2006-03-22 16:00:00','SA_REP',10000,0.2,148,80);
  86. INSERT INTO `employees` VALUES (170,'Tayler','Fox','TFOX','011.44.1343.729268','2006-01-23 16:00:00','SA_REP',9600,0.2,148,80);
  87. INSERT INTO `employees` VALUES (171,'William','Smith','WSMITH','011.44.1343.629268','2007-02-22 16:00:00','SA_REP',7400,0.15,148,80);
  88. INSERT INTO `employees` VALUES (172,'Elizabeth','Bates','EBATES','011.44.1343.529268','2007-03-23 16:00:00','SA_REP',7300,0.15,148,80);
  89. INSERT INTO `employees` VALUES (173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','2008-04-20 16:00:00','SA_REP',6100,0.1,148,80);
  90. INSERT INTO `employees` VALUES (174,'Ellen','Abel','EABEL','011.44.1644.429267','2004-05-10 16:00:00','SA_REP',11000,0.3,149,80);
  91. INSERT INTO `employees` VALUES (175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','2005-03-18 16:00:00','SA_REP',8800,0.25,149,80);
  92. INSERT INTO `employees` VALUES (176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','2006-03-23 16:00:00','SA_REP',8600,0.2,149,80);
  93. INSERT INTO `employees` VALUES (177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','2006-04-22 16:00:00','SA_REP',8400,0.2,149,80);
  94. INSERT INTO `employees` VALUES (178,'Kimberely','Grant','KGRANT','011.44.1644.429263','2015-12-02 03:22:27','SA_REP',7000,0.15,149,NULL);
  95. INSERT INTO `employees` VALUES (179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','2008-01-03 16:00:00','SA_REP',6200,0.1,149,80);
  96. INSERT INTO `employees` VALUES (180,'Winston','Taylor','WTAYLOR','650.507.9876','2015-12-02 03:21:38','SH_CLERK',3200,NULL,120,50);
  97. INSERT INTO `employees` VALUES (181,'Jean','Fleaur','JFLEAUR','650.507.9877','2015-12-02 03:21:38','SH_CLERK',3100,NULL,120,50);
  98. INSERT INTO `employees` VALUES (182,'Martha','Sullivan','MSULLIVA','650.507.9878','2015-12-02 03:21:38','SH_CLERK',2500,NULL,120,50);
  99. INSERT INTO `employees` VALUES (183,'Girard','Geoni','GGEONI','650.507.9879','2015-12-02 03:21:38','SH_CLERK',2800,NULL,120,50);
  100. INSERT INTO `employees` VALUES (184,'Nandita','Sarchand','NSARCHAN','650.509.1876','2015-12-02 03:21:38','SH_CLERK',4200,NULL,121,50);
  101. INSERT INTO `employees` VALUES (185,'Alexis','Bull','ABULL','650.509.2876','2015-12-02 03:21:38','SH_CLERK',4100,NULL,121,50);
  102. INSERT INTO `employees` VALUES (186,'Julia','Dellinger','JDELLING','650.509.3876','2015-12-02 03:21:38','SH_CLERK',3400,NULL,121,50);
  103. INSERT INTO `employees` VALUES (187,'Anthony','Cabrio','ACABRIO','650.509.4876','2015-12-02 03:21:38','SH_CLERK',3000,NULL,121,50);
  104. INSERT INTO `employees` VALUES (188,'Kelly','Chung','KCHUNG','650.505.1876','2015-12-02 03:21:38','SH_CLERK',3800,NULL,122,50);
  105. INSERT INTO `employees` VALUES (189,'Jennifer','Dilly','JDILLY','650.505.2876','2015-12-02 03:21:38','SH_CLERK',3600,NULL,122,50);
  106. INSERT INTO `employees` VALUES (190,'Timothy','Gates','TGATES','650.505.3876','2015-12-02 03:21:38','SH_CLERK',2900,NULL,122,50);
  107. INSERT INTO `employees` VALUES (191,'Randall','Perkins','RPERKINS','650.505.4876','2015-12-02 03:21:38','SH_CLERK',2500,NULL,122,50);
  108. INSERT INTO `employees` VALUES (192,'Sarah','Bell','SBELL','650.501.1876','2015-12-02 03:21:38','SH_CLERK',4000,NULL,123,50);
  109. INSERT INTO `employees` VALUES (193,'Britney','Everett','BEVERETT','650.501.2876','2015-12-02 03:21:38','SH_CLERK',3900,NULL,123,50);
  110. INSERT INTO `employees` VALUES (194,'Samuel','McCain','SMCCAIN','650.501.3876','2015-12-02 03:21:38','SH_CLERK',3200,NULL,123,50);
  111. INSERT INTO `employees` VALUES (195,'Vance','Jones','VJONES','650.501.4876','2015-12-02 03:21:38','SH_CLERK',2800,NULL,123,50);
  112. INSERT INTO `employees` VALUES (196,'Alana','Walsh','AWALSH','650.507.9811','2015-12-02 03:21:38','SH_CLERK',3100,NULL,124,50);
  113. INSERT INTO `employees` VALUES (197,'Kevin','Feeney','KFEENEY','650.507.9822','2015-12-02 03:21:38','SH_CLERK',3000,NULL,124,50);
  114. INSERT INTO `employees` VALUES (198,'Donald','OConnell','DOCONNEL','650.507.9833','2015-12-02 03:21:38','SH_CLERK',2600,NULL,124,50);
  115. INSERT INTO `employees` VALUES (199,'Douglas','Grant','DGRANT','650.507.9844','2015-12-02 03:21:38','SH_CLERK',2600,NULL,124,50);
  116. INSERT INTO `employees` VALUES (200,'Jennifer','Whalen','JWHALEN','515.123.4444','2015-12-02 03:21:38','AD_ASST',4400,NULL,101,10);
  117. INSERT INTO `employees` VALUES (201,'Michael','Hartstein','MHARTSTE','515.123.5555','2015-12-02 03:21:38','MK_MAN',13000,NULL,100,20);
  118. INSERT INTO `employees` VALUES (202,'Pat','Fay','PFAY','603.123.6666','2015-12-02 03:21:38','MK_REP',6000,NULL,201,20);
  119. INSERT INTO `employees` VALUES (203,'Susan','Mavris','SMAVRIS','515.123.7777','2015-12-02 03:21:38','HR_REP',6500,NULL,101,40);
  120. INSERT INTO `employees` VALUES (204,'Hermann','Baer','HBAER','515.123.8888','2015-12-02 03:21:38','PR_REP',10000,NULL,101,70);
  121. INSERT INTO `employees` VALUES (205,'Shelley','Higgins','SHIGGINS','515.123.8080','2015-12-02 03:21:38','AC_MGR',12008,NULL,101,110);
  122. INSERT INTO `employees` VALUES (206,'William','Gietz','WGIETZ','515.123.8181','2015-12-02 03:21:38','AC_ACCOUNT',8300,NULL,205,110);

1.指定一个雇员ID,查询所有的上级.
查询雇员ID为144的所有上级
  1. select 
  2.     @currentId as _id,
  3.     (select @currentId:=manager_id from employees where employee_id=_id) as manager_id,
  4.     @level:=@level+as level
  5. from 
  6. (select @currentId:=144,@level:=0) vars,
  7. employees
  8. where @currentId is not null;


首先,(select @currentId:=144,@level:=0) vars 指定了雇员的ID
@currentId as _id, 将雇员ID暂存为_id,
在相关子查询中,将_id代入查找该ID的上级ID,并修改@current_id.
最后过滤得到结果.

2.整体查询树型结构.(本小猫首创此方法,无需任何过程和函数辅助,直接一句SQL完成)

效果等同于Oracle
select employee_id,sys_connect_by_path(employee_id,'/') path 
from employees 
start with manager_id is null connect by manager_id=prior employee_id;

  1. select eid,group_concat(_id order by _id,lv desc SEPARATOR '/'from (  
  2.     select   
  3.         @gid:=@cgid,@cgid:=eid,  
  4.         if(@gid=@cgid, @currentId,@currentId:=eid) as _id,       
  5.         (select @currentId:=manager_id from employees where employee_id=_id) as manager_id,  
  6.         if(@gid=@cgid, @level:=@level+1,@level:=0) as lv,  
  7.         eid,employee_id  
  8.     from   
  9.     (select @currentId:=-1,@level:=0,@gid:=-1,@cgid:=-1) vars,  
  10.     (select a.employee_id eid,b.employee_id from employees a,employees b order by a.employee_id) a  
  11. ) c where _id is not null  
  12. group by eid   
  13. order by 2;  


MySQL和Oracle的效果展示



©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页