语法  ： create  view  as  with   a  as () , select  *  from  a  ;

 

 

CREATE VIEW pms_work_hour_view

AS

   WITH t

        AS (SELECT id,

                   create_name AS name,

                   project_number AS parent_id,

                   (end_date - begin_date) AS times,

                   ROUND ( (end_date - begin_date) / 3600000, 2) AS work_hour

              FROM PMS_USER_WORK_HOUR_LOG

             WHERE status = '2'),

        tt

        AS (SELECT a.project_code AS id,

                   a.project_name AS name,

                   splitByUnderLine (a.project_code) AS parent_id,

                   b.times,

                   ROUND (b.times / 3600000, 2) AS work_hour

              FROM PMS_SCHEDULE_MANAGEMENT a

                   LEFT JOIN (  SELECT parent_id, SUM (times) times

                                  FROM t

                              GROUP BY parent_id) b

                      ON b.parent_id = a.project_code),

        ttt

        AS (  SELECT DISTINCT splitByUnderLine ( parent_id) AS id,

                              '' AS name,

                              '0' AS parent_id,

                              SUM (times) AS times,

                              ROUND (SUM (times) / 3600000, 2) AS work_hour

                FROM tt

            GROUP BY parent_id)

   SELECT * FROM t

   UNION

   SELECT * FROM tt

   UNION

   SELECT * FROM ttt;