精华内容
下载资源
问答
  • MySQL层级数据查询

    千次阅读 2020-04-30 21:01:58
    MySQL层级数据查询 address表结构如下: address表有如下数据: 现在创建一个函数,获取地址全称: CREATE DEFINER=`root`@`localhost` FUNCTION `getFullName`(`id` varchar(1000)) RETURNS varchar(1000) ...

    MySQL层级数据查询

    address表结构如下:

    图1

    address表有如下数据:

    在这里插入图片描述

    现在创建一个函数,获取地址全称:

    CREATE DEFINER=`root`@`localhost` FUNCTION `getFullName`(`id` varchar(1000)) RETURNS varchar(1000) CHARSET utf8mb4
        READS SQL DATA
    BEGIN
    
    	DECLARE fullName VARCHAR(1000);
    	DECLARE parentId VARCHAR(1000);
    	DECLARE parentName VARCHAR(1000);
    	
    	set fullName = (SELECT address.`name` FROM address WHERE address.id = id);
    	set parentId = (SELECT address.parentId FROM address WHERE address.id = id);
    	
    	WHILE parentId IS NOT NULL DO
    	    set parentName = (SELECT address.`name` FROM address WHERE address.id = parentId);
    			set fullName = CONCAT(parentName,"/",fullName);
    	    set parentId = (SELECT address.parentId FROM address WHERE address.id = parentId);
        END WHILE;
    	
    	RETURN fullName;
    END
    

    函数测试:

    SELECT
    	`address`.`id` AS `id`,
    	`address`.`name` AS `name`,
    	`getFullName` ( `address`.`id` ) AS `fullName` 
    FROM
    	`address`
    

    在这里插入图片描述

    展开全文
  •  @l := @l + 1 AS LEVEL  FROM  pm_project_staff,  ( SELECT @ids := 2, @l := 0) b  WHERE  @ids IS NOT NULL     ) ID,  pm_project_staff DATA   WHERE  FIND_IN_SET( DATA.staff_id,ID....

    这个能查到父节点下的子节点(不包括父节点)

    SELECT
        staff_id,
        parent_staff_id
        FROM
        (
        SELECT
        t1.staff_id,
        t1.parent_staff_id,
        IF
        ( find_in_set( parent_staff_id, @pids ) > 0, @pids := concat( @pids, ',', staff_id ), 0 ) AS ischild
        FROM
        ( SELECT staff_id, parent_staff_id FROM pm_project_staff t WHERE project_id = 24 ORDER BY parent_staff_id, staff_id ) t1,
        ( SELECT @pids := 2) t2
        ) t3
        WHERE
        ischild != 0

     

    这个能查到父节点下所有子节点(包括父节点)

    SELECT 
    DISTINCT
    DATA.staff_id
    FROM
        (
    SELECT
        @ids AS cids,
        project_id ,
        ( SELECT @ids := GROUP_CONCAT( staff_id ) FROM pm_project_staff WHERE FIND_IN_SET( parent_staff_id, @ids ) and project_id = 24 ) AS sids,
        @l := @l + 1 AS LEVEL 
    FROM
        pm_project_staff,
        ( SELECT @ids := 2, @l := 0) b 
    WHERE
        @ids IS NOT NULL 
        
        ) ID,
        pm_project_staff DATA 
        WHERE
        FIND_IN_SET( DATA.staff_id,ID.cids) 

     

    优化的sql

    SELECT *

    FROM

    (

    SELECT

    find_child_dept_ids ('1') pids

    ) p,

     hr_marketing_achievement hma

    LEFT JOIN mt_staff ms on hma.target_id = ms.staff_id

    WHERE hma.achievement_kbn = 1

    AND IF(hma.dept_id = NULL ,find_in_set(hma.target_id, p.pids), find_in_set(hma.dept_id, p.pids) )

    展开全文
  • MySql层级查询

    2017-07-25 09:40:00
    2019独角兽企业重金招聘Python工程师标准>>> ...
    DROP TABLE IF EXISTS `employees`;
    CREATE TABLE `employees` (
      `employee_id` int(11) NOT NULL,
      `FIRST_NAME` varchar(20) DEFAULT NULL,
      `LAST_NAME` varchar(25) DEFAULT NULL,
      `EMAIL` varchar(25) DEFAULT NULL,
      `PHONE_NUMBER` varchar(20) DEFAULT NULL,
      `HIRE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `JOB_ID` varchar(10) DEFAULT NULL,
      `SALARY` int(11) DEFAULT NULL,
      `commission_pct` float DEFAULT NULL,
      `manager_id` int(11) DEFAULT NULL,
      `department_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`employee_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    
    
    
    INSERT INTO `employees` VALUES (100,'Steven','King','SKING','515.123.4567','2015-12-02 03:21:38','AD_PRES',24000,NULL,NULL,90);
    INSERT INTO `employees` VALUES (101,'Neena','Kochhar','NKOCHHAR','515.123.4568','2015-12-02 03:21:38','AD_VP',17000,NULL,100,90);
    INSERT INTO `employees` VALUES (102,'Lex','De Haan','LDEHAAN','515.123.4569','2015-12-02 03:21:38','AD_VP',17000,NULL,100,90);
    INSERT INTO `employees` VALUES (103,'Alexander','Hunold','AHUNOLD','590.423.4567','2015-12-02 03:21:38','IT_PROG',9000,NULL,102,60);
    INSERT INTO `employees` VALUES (104,'Bruce','Ernst','BERNST','590.423.4568','2015-12-02 03:21:38','IT_PROG',6000,NULL,103,60);
    INSERT INTO `employees` VALUES (105,'David','Austin','DAUSTIN','590.423.4569','2015-12-02 03:21:38','IT_PROG',4800,NULL,103,60);
    INSERT INTO `employees` VALUES (106,'Valli','Pataballa','VPATABAL','590.423.4560','2015-12-02 03:21:38','IT_PROG',4800,NULL,103,60);
    INSERT INTO `employees` VALUES (107,'Diana','Lorentz','DLORENTZ','590.423.5567','2015-12-02 03:21:38','IT_PROG',4200,NULL,103,60);
    INSERT INTO `employees` VALUES (108,'Nancy','Greenberg','NGREENBE','515.124.4569','2015-12-02 03:21:38','FI_MGR',12008,NULL,101,100);
    INSERT INTO `employees` VALUES (109,'Daniel','Faviet','DFAVIET','515.124.4169','2015-12-02 03:21:38','FI_ACCOUNT',9000,NULL,108,100);
    INSERT INTO `employees` VALUES (110,'John','Chen','JCHEN','515.124.4269','2015-12-02 03:21:38','FI_ACCOUNT',8200,NULL,108,100);
    INSERT INTO `employees` VALUES (111,'Ismael','Sciarra','ISCIARRA','515.124.4369','2015-12-02 03:21:38','FI_ACCOUNT',7700,NULL,108,100);
    INSERT INTO `employees` VALUES (112,'Jose Manuel','Urman','JMURMAN','515.124.4469','2015-12-02 03:21:38','FI_ACCOUNT',7800,NULL,108,100);
    INSERT INTO `employees` VALUES (113,'Luis','Popp','LPOPP','515.124.4567','2015-12-02 03:21:38','FI_ACCOUNT',6900,NULL,108,100);
    INSERT INTO `employees` VALUES (114,'Den','Raphaely','DRAPHEAL','515.127.4561','2015-12-02 03:21:38','PU_MAN',11000,NULL,100,30);
    INSERT INTO `employees` VALUES (115,'Alexander','Khoo','AKHOO','515.127.4562','2015-12-02 03:21:38','PU_CLERK',3100,NULL,114,30);
    INSERT INTO `employees` VALUES (116,'Shelli','Baida','SBAIDA','515.127.4563','2015-12-02 03:21:38','PU_CLERK',2900,NULL,114,30);
    INSERT INTO `employees` VALUES (117,'Sigal','Tobias','STOBIAS','515.127.4564','2015-12-02 03:21:38','PU_CLERK',2800,NULL,114,30);
    INSERT INTO `employees` VALUES (118,'Guy','Himuro','GHIMURO','515.127.4565','2015-12-02 03:21:38','PU_CLERK',2600,NULL,114,30);
    INSERT INTO `employees` VALUES (119,'Karen','Colmenares','KCOLMENA','515.127.4566','2015-12-02 03:21:38','PU_CLERK',2500,NULL,114,30);
    INSERT INTO `employees` VALUES (120,'Matthew','Weiss','MWEISS','650.123.1234','2015-12-02 03:21:38','ST_MAN',8000,NULL,100,50);
    INSERT INTO `employees` VALUES (121,'Adam','Fripp','AFRIPP','650.123.2234','2015-12-02 03:21:38','ST_MAN',8200,NULL,100,50);
    INSERT INTO `employees` VALUES (122,'Payam','Kaufling','PKAUFLIN','650.123.3234','2015-12-02 03:21:38','ST_MAN',7900,NULL,100,50);
    INSERT INTO `employees` VALUES (123,'Shanta','Vollman','SVOLLMAN','650.123.4234','2015-12-02 03:21:38','ST_MAN',6500,NULL,100,50);
    INSERT INTO `employees` VALUES (124,'Kevin','Mourgos','KMOURGOS','650.123.5234','2015-12-02 03:21:38','ST_MAN',5800,NULL,100,50);
    INSERT INTO `employees` VALUES (125,'Julia','Nayer','JNAYER','650.124.1214','2015-12-02 03:21:38','ST_CLERK',3200,NULL,120,50);
    INSERT INTO `employees` VALUES (126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','2015-12-02 03:21:38','ST_CLERK',2700,NULL,120,50);
    INSERT INTO `employees` VALUES (127,'James','Landry','JLANDRY','650.124.1334','2015-12-02 03:21:38','ST_CLERK',2400,NULL,120,50);
    INSERT INTO `employees` VALUES (128,'Steven','Markle','SMARKLE','650.124.1434','2015-12-02 03:21:38','ST_CLERK',2200,NULL,120,50);
    INSERT INTO `employees` VALUES (129,'Laura','Bissot','LBISSOT','650.124.5234','2015-12-02 03:21:38','ST_CLERK',3300,NULL,121,50);
    INSERT INTO `employees` VALUES (130,'Mozhe','Atkinson','MATKINSO','650.124.6234','2015-12-02 03:21:38','ST_CLERK',2800,NULL,121,50);
    INSERT INTO `employees` VALUES (131,'James','Marlow','JAMRLOW','650.124.7234','2015-12-02 03:21:38','ST_CLERK',2500,NULL,121,50);
    INSERT INTO `employees` VALUES (132,'TJ','Olson','TJOLSON','650.124.8234','2015-12-02 03:21:38','ST_CLERK',2100,NULL,121,50);
    INSERT INTO `employees` VALUES (133,'Jason','Mallin','JMALLIN','650.127.1934','2015-12-02 03:21:38','ST_CLERK',3300,NULL,122,50);
    INSERT INTO `employees` VALUES (134,'Michael','Rogers','MROGERS','650.127.1834','2015-12-02 03:21:38','ST_CLERK',2900,NULL,122,50);
    INSERT INTO `employees` VALUES (135,'Ki','Gee','KGEE','650.127.1734','2015-12-02 03:21:38','ST_CLERK',2400,NULL,122,50);
    INSERT INTO `employees` VALUES (136,'Hazel','Philtanker','HPHILTAN','650.127.1634','2015-12-02 03:21:38','ST_CLERK',2200,NULL,122,50);
    INSERT INTO `employees` VALUES (137,'Renske','Ladwig','RLADWIG','650.121.1234','2015-12-02 03:21:38','ST_CLERK',3600,NULL,123,50);
    INSERT INTO `employees` VALUES (138,'Stephen','Stiles','SSTILES','650.121.2034','2015-12-02 03:21:38','ST_CLERK',3200,NULL,123,50);
    INSERT INTO `employees` VALUES (139,'John','Seo','JSEO','650.121.2019','2015-12-02 03:21:38','ST_CLERK',2700,NULL,123,50);
    INSERT INTO `employees` VALUES (140,'Joshua','Patel','JPATEL','650.121.1834','2015-12-02 03:21:38','ST_CLERK',2500,NULL,123,50);
    INSERT INTO `employees` VALUES (141,'Trenna','Rajs','TRAJS','650.121.8009','2015-12-02 03:21:38','ST_CLERK',3500,NULL,124,50);
    INSERT INTO `employees` VALUES (142,'Curtis','Davies','CDAVIES','650.121.2994','2015-12-02 03:21:38','ST_CLERK',3100,NULL,124,50);
    INSERT INTO `employees` VALUES (143,'Randall','Matos','RMATOS','650.121.2874','2015-12-02 03:21:38','ST_CLERK',2600,NULL,124,50);
    INSERT INTO `employees` VALUES (144,'Peter','Vargas','PVARGAS','650.121.2004','2015-12-02 03:21:38','ST_CLERK',2500,NULL,124,50);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    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);
    INSERT INTO `employees` VALUES (180,'Winston','Taylor','WTAYLOR','650.507.9876','2015-12-02 03:21:38','SH_CLERK',3200,NULL,120,50);
    INSERT INTO `employees` VALUES (181,'Jean','Fleaur','JFLEAUR','650.507.9877','2015-12-02 03:21:38','SH_CLERK',3100,NULL,120,50);
    INSERT INTO `employees` VALUES (182,'Martha','Sullivan','MSULLIVA','650.507.9878','2015-12-02 03:21:38','SH_CLERK',2500,NULL,120,50);
    INSERT INTO `employees` VALUES (183,'Girard','Geoni','GGEONI','650.507.9879','2015-12-02 03:21:38','SH_CLERK',2800,NULL,120,50);
    INSERT INTO `employees` VALUES (184,'Nandita','Sarchand','NSARCHAN','650.509.1876','2015-12-02 03:21:38','SH_CLERK',4200,NULL,121,50);
    INSERT INTO `employees` VALUES (185,'Alexis','Bull','ABULL','650.509.2876','2015-12-02 03:21:38','SH_CLERK',4100,NULL,121,50);
    INSERT INTO `employees` VALUES (186,'Julia','Dellinger','JDELLING','650.509.3876','2015-12-02 03:21:38','SH_CLERK',3400,NULL,121,50);
    INSERT INTO `employees` VALUES (187,'Anthony','Cabrio','ACABRIO','650.509.4876','2015-12-02 03:21:38','SH_CLERK',3000,NULL,121,50);
    INSERT INTO `employees` VALUES (188,'Kelly','Chung','KCHUNG','650.505.1876','2015-12-02 03:21:38','SH_CLERK',3800,NULL,122,50);
    INSERT INTO `employees` VALUES (189,'Jennifer','Dilly','JDILLY','650.505.2876','2015-12-02 03:21:38','SH_CLERK',3600,NULL,122,50);
    INSERT INTO `employees` VALUES (190,'Timothy','Gates','TGATES','650.505.3876','2015-12-02 03:21:38','SH_CLERK',2900,NULL,122,50);
    INSERT INTO `employees` VALUES (191,'Randall','Perkins','RPERKINS','650.505.4876','2015-12-02 03:21:38','SH_CLERK',2500,NULL,122,50);
    INSERT INTO `employees` VALUES (192,'Sarah','Bell','SBELL','650.501.1876','2015-12-02 03:21:38','SH_CLERK',4000,NULL,123,50);
    INSERT INTO `employees` VALUES (193,'Britney','Everett','BEVERETT','650.501.2876','2015-12-02 03:21:38','SH_CLERK',3900,NULL,123,50);
    INSERT INTO `employees` VALUES (194,'Samuel','McCain','SMCCAIN','650.501.3876','2015-12-02 03:21:38','SH_CLERK',3200,NULL,123,50);
    INSERT INTO `employees` VALUES (195,'Vance','Jones','VJONES','650.501.4876','2015-12-02 03:21:38','SH_CLERK',2800,NULL,123,50);
    INSERT INTO `employees` VALUES (196,'Alana','Walsh','AWALSH','650.507.9811','2015-12-02 03:21:38','SH_CLERK',3100,NULL,124,50);
    INSERT INTO `employees` VALUES (197,'Kevin','Feeney','KFEENEY','650.507.9822','2015-12-02 03:21:38','SH_CLERK',3000,NULL,124,50);
    INSERT INTO `employees` VALUES (198,'Donald','OConnell','DOCONNEL','650.507.9833','2015-12-02 03:21:38','SH_CLERK',2600,NULL,124,50);
    INSERT INTO `employees` VALUES (199,'Douglas','Grant','DGRANT','650.507.9844','2015-12-02 03:21:38','SH_CLERK',2600,NULL,124,50);
    INSERT INTO `employees` VALUES (200,'Jennifer','Whalen','JWHALEN','515.123.4444','2015-12-02 03:21:38','AD_ASST',4400,NULL,101,10);
    INSERT INTO `employees` VALUES (201,'Michael','Hartstein','MHARTSTE','515.123.5555','2015-12-02 03:21:38','MK_MAN',13000,NULL,100,20);
    INSERT INTO `employees` VALUES (202,'Pat','Fay','PFAY','603.123.6666','2015-12-02 03:21:38','MK_REP',6000,NULL,201,20);
    INSERT INTO `employees` VALUES (203,'Susan','Mavris','SMAVRIS','515.123.7777','2015-12-02 03:21:38','HR_REP',6500,NULL,101,40);
    INSERT INTO `employees` VALUES (204,'Hermann','Baer','HBAER','515.123.8888','2015-12-02 03:21:38','PR_REP',10000,NULL,101,70);
    INSERT INTO `employees` VALUES (205,'Shelley','Higgins','SHIGGINS','515.123.8080','2015-12-02 03:21:38','AC_MGR',12008,NULL,101,110);
    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的所有上级
      首先(SELECT @currentId := 144,@level := 0) vars 制定雇员的id
      @currentId 将雇员id存为_id, 在相关子查询中,将_id代入查找该ID的上级ID,并修改@current_id. 最后过滤得到结果.
    	select
    			@currentId as _id,
    			(select @currentId:=manager_id from employees where employee_id=_id) as manager_id,
    			@level:=@level+1 as level
    	from 
    	(select @currentId:=191,@level:=0) vars,
    	employees
    	where @currentId is not null;
    
    
    
    
    
    
    效果等同于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;
    
    
    	select eid,group_concat(_id order by _id,lv desc SEPARATOR '/') from (  
    			select   
    					@gid:=@cgid,@cgid:=eid,  
    					if(@gid=@cgid, @currentId,@currentId:=eid) as _id,       
    					(select @currentId:=manager_id from employees where employee_id=_id) as manager_id,  
    					if(@gid=@cgid, @level:=@level+1,@level:=0) as lv,  
    					eid,employee_id  
    			from   
    			(select @currentId:=-1,@level:=0,@gid:=-1,@cgid:=-1) vars,  
    			(select a.employee_id eid,b.employee_id from employees a,employees b order by a.employee_id) a  
    	) c where _id is not null  
    	group by eid   
    	order by 2; 
    
    
    show variables  like 'port';
    
    

     

    转载于:https://my.oschina.net/u/2253438/blog/1486678

    展开全文
  • 二、查询 1、通过普通的sql查询 查询 ASSESS_ID=0的所有子级,并返回查询的子级层级 SELECT DISTINCT c1.level-1 level, c2.assess_id,c2.assess_parent_id FROM( SELECT @ids as _ids, ( SELECT @ids := GROUP_...

    一、创建表、添加测试数据

    create table CS_INTCTL_ASSESS_ITEM_GATHER
    (
     id int(11) PRIMARY key auto_increment,
     assess_id   VARCHAR(36),
     assess_parent_id  VARCHAR(36),
     name        VARCHAR(256)
    )
    
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ( 'c46a67ffa5dc4cd990e9402dd5f21e56', '7540f5592a794ec0982bbea817d4b8ce', '一、组织结构评价(20分)');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('7d77b36468e04e96a56af4f105225fac', '7540f5592a794ec0982bbea817d4b8ce', '二、经营理念与风格评价(14分)');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('7540f5592a794ec0982bbea817d4b8ce', 'd24e44cbc0c34452b1da93739adfe716', '内部环境');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('15f1abe4b9544f7d900f6ccec9842256', '13af09d9e8ad4da297e72e3020e290c8', '一、信贷基础管理评价(10分)');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('73a60d0ed27c40718b3fd707400416af', '13af09d9e8ad4da297e72e3020e290c8', '二、评级授信评价(15分)');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('13af09d9e8ad4da297e72e3020e290c8', '08c080e87e134183b239cc7409be5210', '信贷业务');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('08c080e87e134183b239cc7409be5210', 'd24e44cbc0c34452b1da93739adfe716', '控制活动(55%)');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('d24e44cbc0c34452b1da93739adfe716', '0', '本部评价(70%)');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('c46a67ffa5dc4cd990e9402dd5f21e56', '7540f5592a794ec0982bbea817d4b8ce', '一、组织结构评价(20分)');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('7d77b36468e04e96a56af4f105225fac', '7540f5592a794ec0982bbea817d4b8ce', '二、经营理念与风格评价(14分)');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('7540f5592a794ec0982bbea817d4b8ce', 'd24e44cbc0c34452b1da93739adfe716', '内部环境');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('15f1abe4b9544f7d900f6ccec9842256', '13af09d9e8ad4da297e72e3020e290c8', '一、信贷基础管理评价(10分)');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('73a60d0ed27c40718b3fd707400416af', '13af09d9e8ad4da297e72e3020e290c8', '二、评级授信评价(15分)');
    insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
    values ('13af09d9e8ad4da297e72e3020e290c8', '08c080e87e134183b239cc7409be5210', '信贷业务');
    

    二、查询

    1、通过普通的sql查询
    查询 ASSESS_ID=0的所有子级,并返回查询的子级层级

    SELECT DISTINCT c1.level-1 level, c2.assess_id,c2.assess_parent_id FROM( 
    	SELECT 
    	@ids as _ids, 
    	( SELECT @ids := GROUP_CONCAT(assess_id) 
    	FROM cs_intctl_assess_item_gather 
    	WHERE FIND_IN_SET(assess_parent_id, @ids) 
    	) as cids, 
    	@l := @l+1 as level 
    	FROM cs_intctl_assess_item_gather, 
    	(SELECT @ids :='0', @l := 0 ) b 
    	WHERE @ids IS NOT NULL 
    ) c1, cs_intctl_assess_item_gather c2 
    WHERE FIND_IN_SET(c2.assess_id, c1._ids)
    ORDER BY level, assess_id
    

    在这里插入图片描述
    2、通过定义mysql函数实现

    -- 定义mysql函数
    DROP FUNCTION IF EXISTS queryChildrenInfo;DELIMITER ;;
    CREATE FUNCTION queryChildrenInfo(areaId INT)
    RETURNS VARCHAR(4000)
    BEGIN
    DECLARE sTemp VARCHAR(4000);
    DECLARE sTempChd VARCHAR(4000);
    SET sTemp='$';
    SET sTempChd = CAST(areaId AS CHAR);
    
    WHILE sTempChd IS NOT NULL DO
    SET sTemp= CONCAT(sTemp,',',sTempChd);
    SELECT GROUP_CONCAT(assess_id) INTO sTempChd FROM CS_INTCTL_ASSESS_ITEM_GATHER WHERE FIND_IN_SET(assess_parent_id,sTempChd)>0;
    END WHILE;
    RETURN sTemp;
    END;;
    DELIMITER ;
    
    -- 调用函数
    SELECT DISTINCT assess_id,assess_parent_id FROM CS_INTCTL_ASSESS_ITEM_GATHER WHERE FIND_IN_SET(assess_id,queryChildrenInfo('0'))
    
    展开全文
  • MySql 递归查询 层级

    2020-09-22 17:02:02
    Sql:查询编码为1的directory的所有子级 select F_Id from ( select t1.F_Id, if(find_in_set(F_ParentId, @pids) > 0, @pids := concat(@pids, ',', F_Id), 0) as ischild from ( select F_Id,F_Pa...
  • 场景: 因业务需要,查询某个菜单节点下的... `level` tinyint(2) '菜单级别,1、2、3..', 数据结构: 查询SQL实现方式: SELECT id,`name` FROM ( SELECT t1.id,t1.`name`, IF(FIND_IN_SET(father_id,@pid
  • 层级的业务数据在系统中很常见,...在MySQL中做递归遍历,(Oracle有递归的语法支持,而MySQL并没有),需要自己写函数去递归。此方法效率低。 程序运行基于效率要求,一般使用Redis去递归。本次,因为初始化数据需...
  • 省市区一体表(含mysql版和sql版)带level层级,带数据
  • Oracle的Start with...Connect By实现了树的递归查询,但是现在要求用MYSQL也实现相同的递归查询树的功能。这个功能是我从以前从未使用到过的,于是,我上网查询、找了一些资料开始做了起来。 原始的oracle语句为...
  • 最近要做一个系统 里面带会员推荐的, 无限层的会员推荐,这个怎么能一次性查询到一个ID下 所有用户信息?
  • 1 food NULL NULL NULL NULL 2 snacks 1 1 NULL NULL 3 cakes 2 1 2 NULL 4 birthdays 3 1 2 3 With this, I can easily get the IDs of every parent level of a given category. I have tried doing this using ...
  • 层级结构查询 Hierarchical Queries ID/PID 设计方案 左右值 ... 实现 :此方案资料较多,随机列出一些供参考 ① 左右值编码树形结构数据存储方案 ② MySQL层级结构-树搜索 ③ 采用左右值编码实现无限分级树形结构
  • CREATE DEFINER=`root`@`%` FUNCTION `getparents`(`rootId` int) RETURNS varchar(255) CHARSET utf8 BEGIN DECLARE i varchar(100) ; DECLARE n varchar(100) default ''; DECLARE x varchar(100) default '';...
  • 递归查询用于查询树形...mysql需要使用存储函数,oracle可以使用connect by语句直接查询MySQL递归查询 首先创建一张表 DROP TABLE IF EXISTS `tb_base_region`; CREATE TABLE `tb_base_region` ( `PKID` ...
  • 以前用函数写递归和find_in_set()写过查询某个节点下的所有子节点,因为这次业务数据量不多,尝试了下用一条sql实现N个节点下的所有层级的子节点,或者查询某个节点在tree中的路径。 表数据: id tree_name ...
  • mysql递归查询

    千次阅读 2018-09-15 13:30:47
    每一个商户可以有上级商户,现在需求是一次性查询出 商户对应的所有下级,这里要用到递归查询,折腾了半天把sql弄出来了,记录一下 SELECT B.MERCHANT_ID, A. LEVEL FROM ( SELECT @ids AS _ids, ( ...
  • mysql 递归查询

    2019-04-29 16:18:50
    转自 1、创建表: 复制代码 DROP TABLE IF EXISTS t_areainfo; CREATE TABLE t_areainfo ( ...level int(11) DEFAULT ‘0’, name varchar(255) DEFAULT ‘0’, parentId int(11) DEFAULT ‘0’, sta...
  • 前两天在写SQL的时候 无意间发现这样一条语句 我得天呐 我都惊讶了 还能这样搞的 以前用MYSQL的时候 像菜单这种层级查询的话还要去查询每一条 然后再进行一个递归 封装 麻烦的一* Oracle竟然一条语句就搞定了 这不...
  • 目前需要将mysql中的论文数据转为json格式,但是其中合作作者一列中多个人民通过封号隔开,最后转化样式如下:
  • MySQL】无限层级数据表设计

    千次阅读 2019-06-12 14:11:37
    用户A介绍B入会,B成为A的下线,B再发展C、D等下线,C、D也允许发展下线,不限制层级,但是每个人只允许有一个上线; 类似“传销”; 典型的树结构; 问题: 快速查询某人的所有上线; 快速查询某人的所有...
  • Mysql 省市区字典(带层级,带经纬度,带拼音) https://blog.csdn.net/qq_24909089/article/details/80506981
  • Mysql】树路径,层级

    千次阅读 2016-11-08 17:36:35
    -- -------------节点名只能是数字,且根节点为1,父节点出现在子节点之前才能出正确结果 CREATE TABLE `treenodes` ( ...命名,使父节点和子节点存在关联关系,这样会对查询有很多好处 */
  • mysql 直接的递归查询

    2019-07-26 12:03:02
    目前的mysql版本中并不支持直接的递归查询,但是通过递归到迭代转化的思路,可以在一句SQL内实现树的递归查询下面给一个小示例,后续在贴上调用的代码(代码会贴在最后请注意)MySQL递归查询所有子节点,树形结构查询--...
  •  /*层级*/ DECLARE vTreeId INT; /*树ID*/ DECLARE aff INT; /* SQL 影响记录条数 */ DECLARE af INT DEFAULT 0; /* 总影响记录条数*/ /* 目标节点的右值、treeId和Level值 */ SELECT `rightId`,...
  • mysql级联查询指定节点下面的所有子节点sql模板如下: sql模板如下: SELECT id, pid FROM ( SELECT t1.id, t1.pid, IF ( find_in_set(pid, @pids) > 0, @pids := concat(@pids, ',', id), ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,592
精华内容 1,436
关键字:

mysql层级查询level

mysql 订阅