我下面有联接查询。
select
SD.id,
SD.Scenario,
PR.Type,
PR.StationName,
max(if(PARAM='minH',Value,' ')) as 'Min H',
max(if(PARAM='maxQ',Value,' ')) as 'Max Q',
max(if(PARAM='avQ',Value,' ')) as 'AV Q',
dsd.Dam_Min_Level,
dsd.Dam_Max_Level
from sgwebdb.param_reference as PR
Inner join sgwebdb.scenario_data as SD
ON PR.Param_Id = SD.Param_Id
INNER JOIN sgwebdb.qualicision_detail as Q
ON SD.SCENARIO = Q.Alternative
INNER JOIN sgwebdb.dam_station_detail as dsd
ON dsd.Station_Id = PR.Station_Id
where PR.Type = 'Reservoirs'
and Q.Alternative = 'C'
GROUP BY PR.Id;
并且输出低于
ID Scenario Type StationName Min H Max Q AV Q Dam_Min_Level Dam_Max_Level
3 C Reservoirs Beris 82.1461 23 33
7 C Reservoirs Muda 90.6169 4.8 4.4
11 C Reservoirs Beris 1.49 23 33
15 C Reservoirs Muda 0 4.8 4.4
19 C Reservoirs Pedu 36.262 23 33
如何将0数字替换为Min H,Max Q和AV Q列的空白单元格。
最佳答案
你可以这样
select
SD.id,
SD.Scenario,
PR.Type,
PR.StationName,
max(if(PARAM='minH',Value,0)) as 'Min H',
max(if(PARAM='maxQ',Value,0)) as 'Max Q',
max(if(PARAM='avQ',Value,0)) as 'AV Q',
dsd.Dam_Min_Level,
dsd.Dam_Max_Level
from sgwebdb.param_reference as PR
Inner join sgwebdb.scenario_data as SD
ON PR.Param_Id = SD.Param_Id
INNER JOIN sgwebdb.qualicision_detail as Q
ON SD.SCENARIO = Q.Alternative
INNER JOIN sgwebdb.dam_station_detail as dsd
ON dsd.Station_Id = PR.Station_Id
where PR.Type = 'Reservoirs'
and Q.Alternative = 'C'
GROUP BY PR.Id;
唯一需要做的就是在
IF
条件下将0作为第二个参数而不是空字符串关于mysql - 连接查询中的默认空值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/25400614/