视图

mysql 的视图就是将几个sql查询语句，联合起来，提供一个快速访问的表

-- 新建视图
CREATE VIEW `视图名` AS
-- 查询语句
SELECT
-- 拼接名称
    concat('前缀', `t1`.`id`) AS `num`,
-- if 判断   条件，执行，否则
IF (
    (
    -- 判断是否为空（仅判断数字或者时间，文字不行）
        `t1`.`time`
        AND (
            `t1`.`time` <> '0000-00-00'
        )
    ),
    `t1`.`time1`,
    `t1`.`time2`
) AS `time`,

IF (
    (
        (
            `t1`.`time`
            AND (
                `t1`.`time` <> '1000-01-01 00:00:00'
            )
        )
    -- or 或者
        OR (
            `t1`.`time2`
            AND (
                `t1`.`time2` <> '0000-00-00 00:00:00'
            )
        )
    ),
    '已上线',
    '未上线'
) AS `statusTxt`,
--多重判断，LENGTH(`t1`.`fwef`)>1 文字是否存在
 (case
    when (LENGTH(`t1`.`fwef`)>1 and `t1`.`fwef` <> 'closed') then `t1`.`assignedTo`
    when (`t1`.`fwef` <=> 'closed' and LENGTH(`t1`.`fwef`)>1) then `t1`.`assignedAgo`
  -- 默认值
    ELSE `t1`.`fwef`
    end
    ) as `fwef`,
   -- 添加空，数量名要统一
 null AS `fwefsdas`,`,
FROM
    (
                (
                    (
                        (
                            `表名` `t1`
                            LEFT JOIN `表名` `t2` ON ((`t1`.`product` = `t2`.`id`))
                        )
                        LEFT JOIN `表名` `t3` ON ((`t2`.`dept` = `t3`.`id`))
                    )
                    JOIN `表名` `t4` ON ((`t4`.`story` = `t1`.`id` and `t4`.`version` = `t1`.`version`))
                )
        LEFT JOIN `表名` `t7` ON ((`t7`.`id` = `t1`.`branch`))
    )
WHERE
    (
        (
            `t1`.`fewfewr` IN ('0', '2')
        )
        AND
    -- 一样的if判断
        IF (
            (
                `t1`.`fewfewr` = '0000-00-00'
            ),
            (
                `t1`.`fewfewr` >= '2020-01-01'
            ),
            (
                `t1`.`fewfewr` >= '2020-01-01'
            )
        )
    )
  -- 拼接另外一个查询
UNION
    SELECT
        concat('erwee', `t1`.`id`) AS `num`,
        `t1`.`title` AS `title`,
FROM
    (
        (
            (
                (
                    `表名` `t1`
                    LEFT JOIN `表名` `t2` ON ((`t1`.`product` = `t2`.`id`))
                )
                LEFT JOIN `表名` `t3` ON (
                    (`t1`.`id` = `t3`.`feedback`)
                )
            )
            LEFT JOIN `表名` `t4` ON ((`t4`.`id` = `t2`.`dept`))
        )
        JOIN `表名` `t5` ON (
            (
                `t5`.`feedbackID` = `t1`.`id`
            )
        )
    )
WHERE
    (
        (`t1`.`stage` <> 'disused')