MySQL创建表/视图

Question

我不知道标题是否正确，但这里是我的问题。因此，我想使用多个表中的数据（图片如下）创建View中的视图，这些视图代表了几个表中的字段（变电站，后期类型，区域，灯具类型，故障和维护）的维护情况。

这里是表与连接：

我已经莫名其妙地管理和创建像这样（下图），这是我管理使用的代码块创建：

 CREATE OR REPLACE VIEW 
v_lamp_I 
AS 
SELECT 
lamps.id, 
substation.code AS sub_code, 
substation.name AS sub_name, 
lamps.lamp_code, 
post_type.descript AS post_ty, 
lamp_type.descript AS lamp_ty, 
area.descript AS area_name, 
rasvjeta.adress, 
DATE_FORMAT(date_maintenance, "%d.%m.%Y.") AS date_main, 
lamps.geo_long, 
lamps.geo_lat 
FROM lamps 
INNER JOIN substiation ON substiation.id = lamps.substiation_id 
INNER JOIN post_type ON post_type.id = lamps.post_type_id 
INNER JOIN lamp_type ON lamp_type.id = lamps.lamp_type_id 
INNER JOIN area ON area.id = rasvjeta.area_id 
INNER JOIN maintenance ON maintenance.lamps_id = lamps.id 

我已经设法创建视图，但问题是与该视图，我只能看到行/灯（sifra_lampe），这是维护，只有4.在表灯 我有24条目，并只有4条目但是，我希望看到所有24个条目，并且如果该特定灯没有维护，则字段可以用日期格式（00-00-00或可以为NULL）清空，并且对于维护的条目/灯I想成为可见的日期字段。

这是台灯与条目。

这里是带维护日期的视图。正如你所看到的，只有6个条目

我想看看其余的条目，对于没有维护条目可以为空或日期格式这样（00-00-00）和灯维护日期格式可以保持不变，简而言之，我希望看到所有条目不仅仅是维护的条目。谢谢你，对于长期的问题感到抱歉。我不知道如何构建有意义和短小的问题，因此写下了一切。

Answer 1

create or replace view 
v_lamp_I as 
SELECT lamps.id, 
substation.code AS sub_code, 
substation.name AS sub_name, 
lamps.lamp_code, 
post_type.descript AS post_ty, 
lamp_type.descript AS lamp_ty, 
area.descript AS area_name, 
rasvjeta.adress, 
lamps.geo_long, 
lamps.geo_lat 
FROM lamps 
INNER JOIN substiation ON substiation.id = lamps.substiation_id 
INNER JOIN post_type ON post_type.id = lamps.post_type_id 
INNER JOIN lamp_type ON lamp_type.id = lamps.lamp_type_id 
INNER JOIN area ON area.id = rasvjeta.area_id; 


CREATE OR REPLACE VIEW 
v_lamp_new 
AS 
select v_lamp_I.*,DATE_FORMAT(date_maintenance, "%d.%m.%Y.") AS date_main 
from v_lamp_I 
LEFT JOIN maintenance ON v_lamp_I.id = maintenance.lamps_id; 

Answer 2

CREATE OR REPLACE VIEW 
v_lamp_I 
AS 
select tbl_lamp.*,DATE_FORMAT(date_maintenance, "%d.%m.%Y.") AS date_main 
from (SELECT lamps.id, 
substation.code AS sub_code, 
substation.name AS sub_name, 
lamps.lamp_code, 
post_type.descript AS post_ty, 
lamp_type.descript AS lamp_ty, 
area.descript AS area_name, 
rasvjeta.adress, 
lamps.geo_long, 
lamps.geo_lat 
FROM lamps 
INNER JOIN substiation ON substiation.id = lamps.substiation_id 
INNER JOIN post_type ON post_type.id = lamps.post_type_id 
INNER JOIN lamp_type ON lamp_type.id = lamps.lamp_type_id 
INNER JOIN area ON area.id = rasvjeta.area_id) as tbl_lamp 
LEFT JOIN maintenance ON tbl_lamp.id = maintenance.lamps_id