12 metabase查询-查询某个车间下的网关信息及其他查询

在navicat中创建视图，考虑3表做关联查询较复杂。

select distinct `sg`.`id` AS `网关ID`,`sg`.`gateway_code` AS `网关代码`,`sg`.`remark` AS `网关备注`,`sg`.`Data_Status` AS `网关状态`,`sg`.`ip_address` AS `网关WAN-IP`,`sc`.`code` AS `机柜代码`,`sc`.`remark` AS `机柜备注`,`sw`.`code` AS `车间代码`,`sw`.`fname` AS `车间名称` from ((`scada_gateway` `sg` join `scada_cabinet` `sc`) join `scada_workshop` `sw`) where ((`sg`.`cabinet_id` = `sc`.`id`) and (`sc`.`workshop_id` = `sw`.`fid`))

SELECT
	scada_workshop_gateway_view.`网关ID`, 
	scada_workshop_gateway_view.`网关代码`, 
	scada_workshop_gateway_view.`网关备注`, 
	scada_workshop_gateway_view.`网关状态`, 
	scada_workshop_gateway_view.`网关WAN-IP`, 
	scada_workshop_gateway_view.`机柜代码`, 
	scada_workshop_gateway_view.`机柜备注`, 
	scada_workshop_gateway_view.`车间代码`
FROM
	scada_workshop_gateway_view
WHERE
	scada_workshop_gateway_view.`车间名称` = '熔铸厂熔铸二部'
ORDER BY
	scada_workshop_gateway_view.`网关ID` ASC

 保存：

 查询网关中的PLC分布情况：

SELECT DISTINCT
	sg.gateway_code AS `网关代码`, 
	COUNT(sp.gateway_id) AS `PLC数`
FROM
	scada_plc AS sp,
	scada_gateway AS sg
WHERE
	sp.gateway_id = sg.id
GROUP BY
	sp.gateway_id
ORDER BY
	sp.fid ASC

 保存：

 查询某个网关中的PLC信息：

SELECT DISTINCT
	sp.fid AS PLCID, 
	sp.ip_address AS `IP地址`, 
	sp.port_number AS `端口号`, 
	sp.Rack_No AS `槽号`, 
	sp.Slot_No AS `机架`, 
	sp.Fmodel AS `型号`, 
	sp.FName AS `名称`
FROM
	scada_plc AS sp
WHERE
	sp.gateway_id = (SELECT sg.id FROM scada_gateway sg WHERE sg.gateway_code={{GatewayCode}})
ORDER BY
	sp.fid ASC

保存：

 

 查询PLC中对应设备的分布情况：

SELECT DISTINCT
	sp.FName AS `名称`, 
	COUNT(se.plc_id) AS `设备数`
FROM
	scada_equipment AS se,
	scada_plc AS sp
WHERE
	se.plc_id = sp.fid
GROUP BY
	se.plc_id
ORDER BY
	se.id ASC

 

保存：

 

查询某个PLC中对应的设备：

SELECT DISTINCT
	se.id AS `设备ID`, 
	se.`name` AS `设备名称`
FROM
	scada_equipment AS se
WHERE
	se.plc_id = (SELECT sp.fid FROM scada_plc sp WHERE sp.FName={{PLCName}})
ORDER BY
	se.id ASC

 

保存：

 

查询设备中的参数分布情况：

 注意：此处查询出的设备数量与设备表中的数量对应不上，暂时不用关注。

SELECT DISTINCT
	se.`name` AS `设备名称`, 
	COUNT(sp.equipment_id) AS `参数量`
FROM
	scada_parameter AS sp,
	scada_equipment AS se
WHERE
	sp.equipment_id = se.id
GROUP BY
	sp.equipment_id
ORDER BY
	sp.fid ASC;

保存：

 

查询某个设备的参数：

SELECT DISTINCT
	sp.fid AS `参数ID`, 
	sp.Fname_Cn AS `参数名称-中`, 
	sp.Fname_En AS `参数名称-英`, 
	sp.Address AS `参数地址`, 
	sp.Funit AS `参数单位`, 
	sp.Data_Type AS `数据类型`, 
	sp.DB_Table AS `数据表`, 
	sp.DB_Column AS `数据字段`, 
	sp.fcycle AS `采集周期`, 
	sp.is_aperiodic AS `是否周期`, 
	sp.Data_Status AS `参数状态`, 
	sp.Data_Value AS `参数值`, 
	sp.Data_time AS `采集时间`, 
	sp.Remark AS `备注`
FROM
	scada_parameter AS sp
WHERE
	sp.equipment_id = (SELECT se.id FROM scada_equipment se WHERE se.name = {{EquipmentName}})
ORDER BY
	sp.fid ASC

保存：

 

 查看某个车间下/PLC/机柜等下参数：

涉及多表参数查询，考虑创建视图，然后通过视图进行查看：

创建视图：

SELECT
	`sp`.`fid` AS `参数ID`,
	`sp`.`Fname_Cn` AS `参数名-中`,
	`sp`.`Fname_En` AS `参数名-英`,
	`sp`.`Address` AS `地址`,
	`sp`.`Funit` AS `单位`,
	`sp`.`Data_Type` AS `数据类型`,
	`sp`.`DB_Table` AS `数据表`,
	`sp`.`DB_Column` AS `数据字段`,
	`sp`.`fcycle` AS `循环周期`,
	`sp`.`is_aperiodic` AS `是否周期`,
	`sp`.`Data_Status` AS `数据状态`,
	`sp`.`Data_Value` AS `数据值`,
	`sp`.`Data_time` AS `采集时间`,
	`sp`.`Remark` AS `备注`,
	`se`.`name` AS `设备名称`,
	`spl`.`FName` AS `PLC名称`,
	`sg`.`gateway_code` AS `网关代码`,
	`sc`.`code` AS `机柜代码`,
	`sw`.`code` AS `车间代码`,
	`sw`.`fname` AS `车间名称` 
FROM
	((((( `scada_parameter` `sp` JOIN `scada_equipment` `se` ) JOIN `scada_plc` `spl` ) JOIN `scada_gateway` `sg` ) JOIN `scada_cabinet` `sc` ) JOIN `scada_workshop` `sw` ) 
WHERE
	((
			`sp`.`equipment_id` = `se`.`id` 
			) 
		AND ( `se`.`plc_id` = `spl`.`fid` ) 
		AND ( `spl`.`gateway_id` = `sg`.`id` ) 
		AND ( `sg`.`cabinet_id` = `sc`.`id` ) 
	AND ( `sc`.`workshop_id` = `sw`.`fid` )) 
ORDER BY
	`sp`.`fid`

查看某个车间下的参数：

SELECT
	scada_all_view.`参数ID`, 
	scada_all_view.`参数名-中`, 
	scada_all_view.`参数名-英`, 
	scada_all_view.`地址`, 
	scada_all_view.`单位`, 
	scada_all_view.`数据类型`, 
	scada_all_view.`数据表`, 
	scada_all_view.`数据字段`, 
	scada_all_view.`循环周期`, 
	scada_all_view.`是否周期`, 
	scada_all_view.`数据状态`, 
	scada_all_view.`数据值`, 
	scada_all_view.`采集时间`, 
	scada_all_view.`备注`, 
	scada_all_view.`设备名称`, 
	scada_all_view.`PLC名称`, 
	scada_all_view.`网关代码`, 
	scada_all_view.`机柜代码`, 
	scada_all_view.`车间代码`
FROM
	scada_all_view
WHERE
	scada_all_view.`车间名称` = '熔铸厂熔铸二部'
ORDER BY
	scada_all_view.`参数ID`

 保存：