1. SELECT DISTINCT
    2.     p.id,
    3.     p.create_time,
    4.     p.create_id,
    5. IF( l.id IS NULL, p.`status`, IF ( l.flag = 2, 4, l.`status` ) ) `status`,
    6.     l.id data_id,
    7.     l.if_all_examined,
    8.     p.project_code,
    9.     CONCAT(IFNULL( p.project_name_start, "" ),p.project_name,IFNULL( p.project_name_end, "" )) project_name,
    10.     p.project_type,
    11.     p.investment_type,
    12.     p.project_step,
    13.     p.tbbm,
    14.     p.zzj,
    15.     p.xmfzr,
    16.     p.main_content,
    17.     p.construction_nature,
    18.     p.project_lx,
    19.     p.gnlb,
    20.     p.type,
    21.     p.flag,
    22. CASE
    23.         WHEN p.STATUS = 6 
    24.         THEN
    25.         p.update_time ELSE "" 
    26.     END AS finishDate,
    27.     p.fm_time,
    28.     p.parent_id,
    29.     p.examine_dept,
    30.     p.if_child,
    31.     p.child_num,
    32.     p.if_tzfq,
    33.     p.file_url,
    34.     p.relation_project_code,
    35.     p.construction_child_type,
    36.     p.done_time 
    37. FROM
    38.     project p
    39.     INNER JOIN project_build cl ON p.id = cl.project_id 
    40.     AND ( cl.`status` = '4' OR cl.flag = '2' )
    41.     LEFT JOIN project_jdys l ON p.id = l.project_id 
    42.     AND l.if_history = 0
    43.     INNER JOIN project_step s ON (( p.project_step = s.now_step AND p.project_type = s.project_type AND s.project_step = 18 ) OR l.id IS NOT NULL ) 
    44. WHERE
    45.     p.del_flag = 0 
    46. AND
    47. IF
    48.     ( p.project_step = 5, p.step_status = '4', 1 = 1 ) 
    49.     AND (( p.flag = 2 AND p.`status` IN ( 1, 2, 3, 4, 5 )) OR p.`status` = '4' ) 
    50.     AND p.if_child = '0' 
    51. ORDER BY
    52.     p.create_time DESC
    1. UPDATE 
    2. project pt INNER JOIN
    3. (SELECT count(*) num,pt.parent_id FROM project pt
    4. WHERE pt.parent_id in 
    5. (SELECT id FROM project 
    6. WHERE if_child = 0) 
    7. GROUP BY pt.parent_id) p
    8. ON pt.id = p.parent_id
    9. SET pt.child_num = p.num-1