MySQL 递归查询，联表更新

自己做的笔记
1、mysql 递归查询语句：

WITH RECURSIVE cte AS
                            (
                                SELECT a.Id , a.name FROM t_base a WHERE a.Id = '1' 
                                UNION ALL
                                SELECT k.Id ,CONCAT(c.name, ' > ' ,k.name )  FROM t_baseStruct k INNER JOIN cte c ON c.Id = k.Pid WHERE k.isDel = '0' 
                            )SELECT Id ,name FROM cte

递归查询，查询人员ID为56 下的所有下级人员

SELECT b.* FROM t_safetyperson b WHERE b.safetyPersonId = 56
    UNION ALL
    (WITH RECURSIVE cte AS
    (
    SELECT a.* FROM t_safetyperson a WHERE a.safetyPersonPid =56
    UNION ALL
    SELECT k.* FROM t_safetyperson k INNER JOIN cte c ON c.safetyPersonId = k.safetyPersonPid
    )SELECT * FROM cte)

2、联表更新

UPDATE t_rawpersondata a, (SELECT LEFT(phoneNumber, 11) AS phoneNumber,rawId FROM  t_rawpersondata) b SET a.`phoneNumber`=b.phoneNumber WHERE a.`rawId`=b.rawId ；

  UPDATE t_club_fielduse t,(SELECT
    fieldUseId
    FROM t_club_fielduse
    WHERE useStatus = 1
    AND isDel = 1
    AND UNIX_TIMESTAMP(NOW()) > UNIX_TIMESTAMP(endTime)) p,
    ( SELECT format( ( TIME_TO_SEC(LEFT(endTime,16))-TIME_TO_SEC(LEFT(startTime,16) ) )/3600,1) as results,fieldUseId FROM t_club_fielduse) s
    SET t.useStatus = 2,
    t.actEndTime = NOW(),
    t.hours = s.results
    WHERE
    t.fieldUseId = p.fieldUseId
    and  t.fieldUseId = s.fieldUseId；