MySQL数据库：创建视图，通过union将两张表连接起来

最新推荐文章于 2024-05-16 03:20:42 发布

25号底片~

最新推荐文章于 2024-05-16 03:20:42 发布

阅读量2.3k

点赞数 1

文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_46001736/article/details/127669836

版权

CREATE VIEW `distribution_select`     //视图名称
AS 
//第一张表
select `fault_information_waitdistribution`.`id` AS `id`,
`fault_information_waitdistribution`.`status` AS `status`,
`fault_information_waitdistribution`.`repair_product` AS `repair_product`,
`fault_information_waitdistribution`.`fault_contact` AS `fault_contact`,
`fault_information_waitdistribution`.`fault_type` AS `fault_type`,
`fault_information_waitdistribution`.`fault_phone` AS `fault_phone`,
`fault_information_waitdistribution`.`fault_address` AS `fault_address`,
`fault_information_waitdistribution`.`fault_phone` AS `fault_description`,
`fault_information_waitdistribution`.`fault_address` AS `fault_photo`,
`fault_information_waitdistribution`.`fault_phone` AS `creation_date`,
`fault_information_waitdistribution`.`fault_address` AS `renew_date`,
`fault_information_waitdistribution`.`maintenance_man_id` AS `maintenance_man_id`
from `fault_information_waitdistribution`
//连接两张表
union 
//第二张表
select `fault_information_allocated`.`id` AS `id`,
`fault_information_allocated`.`status` AS `status`,
`fault_information_allocated`.`repair_product` AS `repair_product`,
`fault_information_allocated`.`fault_contact` AS `fault_contact` ,
`fault_information_allocated`.`fault_type` AS `fault_type`,
`fault_information_allocated`.`fault_phone` AS `fault_phone`,
`fault_information_allocated`.`fault_address` AS `fault_address` ,
`fault_information_allocated`.`fault_phone` AS `fault_description`,
`fault_information_allocated`.`fault_photo` AS `fault_photo`,
`fault_information_allocated`.`creation_date` AS `creation_date`,
`fault_information_allocated`.`renew_date` AS `renew_date` ,
`fault_information_allocated`.`maintenance_man_id` AS `maintenance_man_id` 
from `fault_information_allocated`;

注：使用时需要把注释删除，且两张表中指定的列须完全一致

表一：fault_information_waitdistribution