存在多個(gè)表都有相同的一個(gè)字段a,但是數(shù)據(jù)在表中參差不齊，現(xiàn)在有一個(gè)需求，創(chuàng)建一個(gè)視圖宪躯，包含多表查詢的所有結(jié)果，表中存在null列的數(shù)據(jù)按null值存進(jìn)視圖中位迂。

假設(shè)：有一張表tb_residentadmitnote_info访雪，表中數(shù)據(jù)：

還有另外一張表tb_drinking_history，表中數(shù)據(jù)：

tb_family_history表等其他多個(gè)表：

可以看到掂林，這幾張表的共同點(diǎn)是存在同樣一個(gè)字段patient_unique_id臣缀，這是一個(gè)關(guān)鍵點(diǎn)。現(xiàn)在需求是泻帮，創(chuàng)建一張視圖存放用"select * from tab_name"查詢出的結(jié)果精置。

這其中有一個(gè)關(guān)鍵點(diǎn)就是每個(gè)表中的數(shù)據(jù)行數(shù)是不一致的。如果用簡(jiǎn)單的內(nèi)連接锣杂，視圖中只包含patient_unique_id是1脂倦，2的數(shù)據(jù)，但如果仍然將patient_unique_id為3的數(shù)據(jù)存到視圖中元莫，且視圖中沒有patient_unique_id=3記錄的相關(guān)字段置為null呢赖阻？

首先我們根據(jù)基表創(chuàng)建視圖：

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`)))

以上代碼加粗的部分是核心，首先存在一張基表所有的表的主鍵信息都會(huì)包含踱蠢。

執(zhí)行查詢：

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。

這其實(shí)應(yīng)用到了MySQL中的外連接查詢苇侵。