使用with 创建视图

语法  ： 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;