创建多表查询的结果视图

存在多个表都有相同的一个字段a,但是数据在表中参差不齐，现在有一个需求，创建一个视图，包含多表查询的所有结果，表中存在null列的数据按null值存进视图中。

假设：有一张表tb_residentadmitnote_info，表中数据：

还有另外一张表tb_drinking_history，表中数据：

tb_family_history表等其他多个表：

可以看到，这几张表的共同点是存在同样一个字段patient_unique_id，这是一个关键点。现在需求是，创建一张视图存放用"select * from tab_name"查询出的结果。

这其中有一个关键点就是每个表中的数据行数是不一致的。如果用简单的内连接，视图中只包含patient_unique_id是1，2的数据，但如果仍然将patient_unique_id为3的数据存到视图中，且视图中没有patient_unique_id=3记录的相关字段置为null呢？

首先我们根据基表创建视图：

CREATE

ALGORITHM = UNDEFINED

DEFINER = `root`@`%`

SQL SECURITY DEFINER

VIEW `past_illness` AS

SELECT

`tb_residentadmitnote_info`.`patient_unique_id` AS `patient_unique_id`,

`tb_residentadmitnote_info`.`hospi_time` AS `hospi_time`,

`tb_cardiovascular_disease_history`.`cardiovascular_disease_id` AS `cardiovascular_disease_id`,

`tb_cardiovascular_disease_history`.`cardiovascular_disease_has` AS `cardiovascular_disease_has`,

`tb_cardiovascular_disease_history`.`cardiovascular_disease_name` AS `cardiovascular_disease_name`,

`tb_cardiovascular_disease_history`.`cardiovascular_disease_dtdes_num` AS `cardiovascular_disease_dtdes_num`,

`tb_cardiovascular_disease_history`.`cardiovascular_disease_dtdes_num_mod` AS `cardiovascular_disease_dtdes_num_mod`,

`tb_cardiovascular_disease_history`.`cardiovascular_disease_dtdes_unit` AS `cardiovascular_disease_dtdes_unit`,

`tb_cardiovascular_disease_history`.`cardiovascular_disease_trend` AS `cardiovascular_disease_trend`,

`tb_cardiovascular_disease_history`.`org_id` AS `org_id`,

`tb_cardiovascular_disease_history`.`hospital_id` AS `hospital_id`,

`tb_cardiovascular_disease_history`.`user_id` AS `user_id`,

`tb_cardiovascular_disease_history`.`create_by` AS `create_by`,

`tb_cardiovascular_disease_history`.`create_dt` AS `create_dt`,

`tb_cardiovascular_disease_history`.`update_by` AS `update_by`,

`tb_cardiovascular_disease_history`.`update_dt` AS `update_dt`,

`tb_cardiovascular_disease_history`.`is_enable` AS `is_enable`,

`tb_drinking_history`.`drinking_id` AS `drinking_id`,

`tb_drinking_history`.`drinking_is` AS `drinking_is`,

`tb_drinking_history`.`drinking_date_desc` AS `drinking_date_desc`,

`tb_drinking_history`.`drinking_dtdes_num_mod` AS `drinking_dtdes_num_mod`,

`tb_drinking_history`.`drinking_dtdes_num` AS `drinking_dtdes_num`,

`tb_drinking_history`.`drinking_dtdes_unit` AS `drinking_dtdes_unit`,

`tb_drinking_history`.`drinking_quit_time_date_desc` AS `drinking_quit_time_date_desc`,

`tb_drinking_history`.`drinking_quit_time_num_mod` AS `drinking_quit_time_num_mod`,

`tb_drinking_history`.`drinking_quit_time_dtdes_num` AS `drinking_quit_time_dtdes_num`,

`tb_drinking_history`.`drinking_quit_time_dtdes_unit` AS `drinking_quit_time_dtdes_unit`,

`tb_drinking_history`.`drinking_vol_num` AS `drinking_vol_num`,

`tb_drinking_history`.`drinking_vol_unit` AS `drinking_vol_unit`,

`tb_family_history`.`family_history_id` AS `family_history_id`,

`tb_family_history`.`family_history_is` AS `family_history_is`,

`tb_family_history`.`family_history_Immediate_members` AS `family_history_Immediate_members`,

`tb_family_history`.`family_history_disease` AS `family_history_disease`,

`tb_family_history`.`family_history_infect_diseases` AS `family_history_infect_diseases`,

`tb_family_history`.`family_history_chronic_history` AS `family_history_chronic_history`,

`tb_infectious_diseases_history`.`inf_diseases_history_id` AS `inf_diseases_history_id`,

`tb_infectious_diseases_history`.`infectious_diseases_has` AS `infectious_diseases_has`,

`tb_infectious_diseases_history`.`infectious_diseases_name` AS `infectious_diseases_name`,

`tb_infectious_diseases_history`.`infectious_diseases_dtdes_num` AS `infectious_diseases_dtdes_num`,

`tb_infectious_diseases_history`.`infectious_diseases_dtdes_num_mod` AS `infectious_diseases_dtdes_num_mod`,

`tb_infectious_diseases_history`.`infectious_diseases_dtdes_unit` AS `infectious_diseases_dtdes_unit`,

`tb_infectious_diseases_history`.`infectious_diseases_trend` AS `infectious_diseases_trend`,

`tb_paffected_area_history`.`paffected_area_id` AS `paffected_area_id`,

`tb_paffected_area_history`.`paffected_area_dtdes_num` AS `paffected_area_dtdes_num`,

`tb_paffected_area_history`.`paffected_area_dtdes_num_mod` AS `paffected_area_dtdes_num_mod`,

`tb_paffected_area_history`.`paffected_area_dtdes_unit` AS `paffected_area_dtdes_unit`,

`tb_paffected_area_history`.`paffected_area_name` AS `paffected_area_name`

FROM

(((((`tb_residentadmitnote_info`

LEFT JOIN `tb_drinking_history` ON ((`tb_residentadmitnote_info`.`patient_unique_id` = `tb_drinking_history`.`patient_unique_id`)))

LEFT JOIN `tb_family_history` ON ((`tb_residentadmitnote_info`.`patient_unique_id` = `tb_family_history`.`patient_unique_id`)))

LEFT JOIN `tb_infectious_diseases_history` ON ((`tb_residentadmitnote_info`.`patient_unique_id` = `tb_infectious_diseases_history`.`patient_unique_id`)))

LEFT JOIN `tb_paffected_area_history` ON ((`tb_residentadmitnote_info`.`patient_unique_id` = `tb_paffected_area_history`.`patient_unique_id`)))

LEFT JOIN `tb_cardiovascular_disease_history` ON ((`tb_residentadmitnote_info`.`patient_unique_id` = `tb_cardiovascular_disease_history`.`patient_unique_id`)))

以上代码加粗的部分是核心，首先存在一张基表所有的表的主键信息都会包含。

执行查询：

select patient_unique_id,cardiovascular_disease_id,drinking_id,family_history_id,inf_diseases_history_id,paffected_area_id FROM test.past_illness ;

drinking_id字段所在表不存在patient_unique_id为2，3的值，所以在视图中显示为null。

这其实应用到了MySQL中的外连接查询。