Mysql创建视图

# 2015年之前的数据放到work_before表:
insert into work_before (`idwork`, `work_title`, `work_unit`, `work_ok_unit`, `work_start_date`, `work_end_date`, `create_time`, `update_time`, `delete_time`, `userid`, `xiebanren`, `anjianid`, `taskid`, `work_status`, `work_status_content`, `shenhe_content`, `shenhe_datetime`, `shenhe_userid`, `tagid`, `worktype`) select idwork,work_title,work_unit,work_ok_unit,work_start_date,work_end_date,create_time,update_time,delete_time,userid,xiebanren,anjianid,taskid,work_status,work_status_content,shenhe_content,shenhe_datetime,shenhe_userid,tagid,worktype from work where work_end_date < '2015'

# 2015,2016,2017放到对应的work_2015,work_2016, work_2017表里:
insert into work_2015 (`idwork`, `work_title`, `work_unit`, `work_ok_unit`, `work_start_date`, `work_end_date`, `create_time`, `update_time`, `delete_time`, `userid`, `xiebanren`, `anjianid`, `taskid`, `work_status`, `work_status_content`, `shenhe_content`, `shenhe_datetime`, `shenhe_userid`, `tagid`, `worktype`) 
select idwork,work_title,work_unit,work_ok_unit,work_start_date,work_end_date,create_time,update_time,delete_time,userid,xiebanren,anjianid,taskid,work_status,work_status_content,shenhe_content,shenhe_datetime,shenhe_userid,tagid,worktype from work where work_end_date LIKE '%2017%'

# 创建视图:
CREATE VIEW `work_2015_view`
AS
SELECT
    `concord_lawyer`.`work_2015`.`idwork` AS `idwork`,
    `concord_lawyer`.`work_2015`.`work_title` AS `work_title`,
    `concord_lawyer`.`work_2015`.`work_unit` AS `work_unit`,
    `concord_lawyer`.`work_2015`.`work_ok_unit` AS `work_ok_unit`,
    `concord_lawyer`.`work_2015`.`work_start_date` AS `work_start_date`,
    `concord_lawyer`.`work_2015`.`work_end_date` AS `work_end_date`,
    `concord_lawyer`.`work_2015`.`work_status` AS `work_status`,
    `concord_lawyer`.`work_2015`.`create_time` AS `create_time`,
    `concord_lawyer`.`work_2015`.`update_time` AS `update_time`,
    `concord_lawyer`.`work_2015`.`delete_time` AS `delete_time`,
    `concord_lawyer`.`work_2015`.`taskid` AS `taskid`,
    `concord_lawyer`.`work_2015`.`tagid` AS `tagid`,
    `concord_lawyer`.`work_2015`.`shenhe_userid` AS `shenhe_userid`,
    `user_view`.`iduser` AS `iduser`,
    `user_view`.`user_realname` AS `user_realname`,
    `user_view`.`user_phone` AS `user_phone`,
    `user_view`.`user_email` AS `user_email`,
    `user_view`.`bumen_name` AS `user_bumen`,
    `user_view`.`fensuo_name` AS `user_fensuo`,
    `user_view`.`zhiwei_name` AS `user_zhiwei`,
    `user_view`.`user_lizhi` AS `user_lizhi`,
    `anjian_jianlv_view`.`idanjian` AS `idanjian`,
    `anjian_jianlv_view`.`anjian_number` AS `anjian_number`,
    `anjian_jianlv_view`.`anjian_title` AS `anjian_title`,
    `anjian_jianlv_view`.`zhonglei_name` AS `zhonglei_name`
FROM
    (
        (
            `concord_lawyer`.`work_2015`
        JOIN `concord_lawyer`.`user_view`
        )
    JOIN `concord_lawyer`.`anjian_jianlv_view`
    )
WHERE
    (
        (
            `concord_lawyer`.`work_2015`.`userid` = `user_view`.`iduser`
        ) AND(
            `concord_lawyer`.`work_2015`.`anjianid` = `anjian_jianlv_view`.`idanjian`
        )
    )

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页