业务要求是把四种企业取6个字段放到一张视图里面,在分页查询的时候提高查询效率,四种行业所对应的企业信息表,可想而知,四表是相互独立无关联的,且字段肯定也是天差地别,虽然一些如企业名称,营业执照等字段都有,但字段名肯定是不同的。
如果是mysql数据库,那么很简单,直接
create view viewname
as
select * from table1
union all
select * from table2
union all
select * from table3
union all
select * from table4
select `m`.`id` AS `id`,`m`.`tyshxydm` AS `tyshxydm`,`m`.`qymc` AS `qymc`,
`m`.`xian_name` AS `address` ,m.sendtime as sendtime ,m.type from `mine_enterprise` `m`
union all select `y`.`agent_pk` AS `id`,`y`.`tyshxydm` AS `tyshxydm`,`y`.`scjydwmc` AS `qymc`,
`y`.`scjydwmc` AS `adress` ,y.sendtime as sendtime, y.type from `yh_ds_dwd_gldx_yhbzqy_yhbzscjydwxx` `y`
union all select `w`.`enterpid` AS `id`,`w`.`entcreditcode` AS `tyshxydm`,`w`.`enterpname` AS `qymc`,
`w`.`address_registry` AS `adress` ,w.updatetime as sendtime , w.type from `wh_ds_std_wxhxpfxjcyj_cim_enterprise_saisi` `w`
union all select `t`.`id` AS `id`,`t`.`tyshxydm` AS `tyshxydm`,
`t`.`qymc` AS `qymc`,`t`.`scjydz` AS `adress` ,t.sync_time as sendtime , t.type from `trade_enterprise` `t`