mysql - 我想从mysql的输出中删除这些空值，我将事件列分为3、4、5、6列

SELECT t1.client_name,FROM_UNIXTIME(t1.timestamp,"%d")AS Date,
case whent1.event='ALL_OFF'then
    (SUM(Timestampdiff(MINUTE,FROM_UNIXTIME(t2.timestamp),
         FROM_UNIXTIME(t1.timestamp)))) end as ALL_OFF,
case when t1.event = 'HOME_ON' then
   (SUM(Timestampdiff(MINUTE,FROM_UNIXTIME(t2.timestamp) ,
        FROM_UNIXTIME(t1.timestamp))))  end as HOME_ON,
case when t1.event = 'OFFICE_ON' then
   (SUM(Timestampdiff(MINUTE,FROM_UNIXTIME(t2.timestamp) ,
         FROM_UNIXTIME(t1.timestamp)))) end as OFFICE_ON,
case when t1.event = 'PARTY_ON' then
   (SUM(Timestampdiff(MINUTE,FROM_UNIXTIME(t2.timestamp) ,
        FROM_UNIXTIME(t1.timestamp))))  end as PARTY_ON,
case when t1.event = 'OUTDOOR_ON' then
   (SUM(Timestampdiff(MINUTE,FROM_UNIXTIME(t2.timestamp) ,
         FROM_UNIXTIME(t1.timestamp))))  end as OUTDOOR_ON
FROM logs13 AS t1
JOIN logs13 AS t2 ON (t2.id = t1.id - 1)
       where  t1.wonderFit_ID = '0000000037fd0ef6' and t1.client_name ='Puma'  And FROM_UNIXTIME(t1.timestamp,"%Y")='2017' and FROM_UNIXTIME(t1.timestamp,"%M")='April'  and Timestampdiff(MINUTE,FROM_UNIXTIME(t2.timestamp) , FROM_UNIXTIME(t1.timestamp)) >= 0
group by t1.event, FROM_UNIXTIME(t1.timestamp,"%d")
order by FROM_UNIXTIME(t1.timestamp,"%d");

最佳答案

您期望用什么值代替NULL？

如果需要用0代替NULL，则可以使用以下格式

CASE WHEN expr1 = expr2 THEN value1 ELSE value2 END

例：

CASE when t1.event='ALL_OFF' THEN (SUM(Timestampdiff(MINUTE,FROM_UNIXTIME(t2.timestamp), FROM_UNIXTIME(t1.timestamp)))) ELSE 0 END as ALL_OFF

关于mysql - 我想从mysql的输出中删除这些空值，我将事件列分为3、4、5、6列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46599415/