mysql - 显示相反值的 SQL 查询

Question

处理 sql 查询以显示 T_Temperatur 和 T_Badende_per_Time 及其值。我的 sql 查询使用 Cast 将值的逗号更改为点，这不是问题所在。问题是我的 sql 查询选择了相反标题的值，如您在此处所见:

SQL:

SELECT routines.date, routines.time, 
SUM( IF( measurements.title =  'T_Temperatur', CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ), 0 ) ) AS T_Temperatur, 

SUM( IF( measurements.title =  'T_Badende_per_Time', CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ), 0 ) ) AS Badende,

SUM( IF( measurements.title =  'T_Luft_temperatur', CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ), 0 ) ) AS Luft_Temp

FROM routines
INNER JOIN measure_routine ON routines.id = measure_routine.routine_id
INNER JOIN measurements ON measure_routine.measure_id = measurements.id
GROUP BY routines.date, routines.time
ORDER BY routines.date, routines.time;  

我的数据库是用

建立的

例程:

测量例程:

测量:

Answer 1

交换IF()函数中的参数，如果条件为真，则返回0!

如果(表达式1,表达式2,表达式3)

如果 expr1 为真(expr1 <> 0 且 expr1 <> NULL)，则 IF() 返回 expr2；否则返回 expr3。

SELECT routines.date, routines.time, 
   SUM(IF(measurements.title='T_Temperatur', CAST(REPLACE(routines.value,',','.' ) AS DECIMAL(18,2)), 0)) AS T_Temperatur, 
   SUM(IF(measurements.title='T_Badende_per_Time', CAST(REPLACE(routines.value,',','.') AS DECIMAL(18,2)), 0)) AS Badende
FROM routines
INNER JOIN measure_routine ON routines.id = measure_routine.routine_id
INNER JOIN measurements ON measure_routine.measure_id = measurements.id
order by routines.date, routines.time