遇到四张表数据类似,客户要求在一张列表页面显示,结果不好分页,一开考虑使用视图,结果几个表id有重复,又没法添加自增列,只好加临时表
视图:
create view temp (id , c_title ,c_time, c_type) as
select `e_specialjob`.`id` AS `c_id`,`e_specialjob`.`title` AS `c_title`,`e_specialjob`.`time` AS `c_time`,`e_specialjob`.`dx_status` AS `c_type` from `e_specialjob` where (`e_specialjob`.`status` = 1)
union
select `e_specialwljob`.`id` AS `c_id`,`e_specialwljob`.`title` AS `title`,`e_specialwljob`.`c_time` AS `time`,3 AS `3` from `e_specialwljob` where (`e_specialwljob`.`status` = 1)
union
select `e_specialdailyjob`.`id` AS `c_id`,`e_specialdailyjob`.`title` AS `title`,`e_specialdailyjob`.`time` AS `c_time`,4 AS `4` from `e_specialdailyjob` where (`e_specialdailyjob`.`status` = 1)
union
select `j_fzcqy`.`id` AS `c_id`,`j_fzcqy`.`c_title` AS `c_title`,`j_fzcqy`.`c_date` AS `c_time`,5 AS `5` from `j_fzcqy` where (`j_fzcqy`.`c_zt` = 1)
select `e_specialjob`.`id` AS `c_id`,`e_specialjob`.`title` AS `c_title`,`e_specialjob`.`time` AS `c_time`,`e_specialjob`.`dx_status` AS `c_type` from `e_specialjob` where (`e_specialjob`.`status` = 1)
union
select `e_specialwljob`.`id` AS `c_id`,`e_specialwljob`.`title` AS `title`,`e_specialwljob`.`c_time` AS `time`,3 AS `3` from `e_specialwljob` where (`e_specialwljob`.`status` = 1)
union
select `e_specialdailyjob`.`id` AS `c_id`,`e_specialdailyjob`.`title` AS `title`,`e_specialdailyjob`.`time` AS `c_time`,4 AS `4` from `e_specialdailyjob` where (`e_specialdailyjob`.`status` = 1)
union
select `j_fzcqy`.`id` AS `c_id`,`j_fzcqy`.`c_title` AS `c_title`,`j_fzcqy`.`c_date` AS `c_time`,5 AS `5` from `j_fzcqy` where (`j_fzcqy`.`c_zt` = 1)
临时表:
create table if not exists temp (id int auto_increment not null primary key,c_id int, c_title varchar(50),c_time date, c_type int ) as
select `e_specialjob`.`id` AS `c_id`,`e_specialjob`.`title` AS `c_title`,`e_specialjob`.`time` AS `c_time`,`e_specialjob`.`dx_status` AS `c_type` from `e_specialjob` where (`e_specialjob`.`status` = 1)
union
select `e_specialwljob`.`id` AS `c_id`,`e_specialwljob`.`title` AS `title`,`e_specialwljob`.`time` AS `c_time`,3 AS `3` from `e_specialwljob` where (`e_specialwljob`.`status` = 1)
union
select `e_specialdailyjob`.`id` AS `c_id`,`e_specialdailyjob`.`title` AS `title`,`e_specialdailyjob`.`time` AS `c_time`,4 AS `4` from `e_specialdailyjob` where (`e_specialdailyjob`.`status` = 1)
union
select `j_fzcqy`.`id` AS `c_id`,`j_fzcqy`.`c_title` AS `c_title`,`j_fzcqy`.`c_date` AS `c_time`,5 AS `5` from `j_fzcqy` where (`j_fzcqy`.`c_zt` = 1)
先清空临时表:
truncate table temp;