mysql - 如何应对日期频繁变化(逐日)

Question

我想更改结果中每天的日期。怎么可能

类似的例子:在哪里REPLY_dATE 在“2017-03-17 12:00:00 AM”和“2017-03-17 4:00:00 PM”之间我想替换=====2017-01-01 12:00:00 AM====today_Date 2017-03-17 4:00:00 PM=======当前日期

Answer 1

第一步使用LEAST():

LEAST(264, Round(SUM(TIMESTAMPDIFF(MINUTE,Request_Date, REPLY_dATE))/480)) as WH

使用CASE进行第二步:

CASE WHEN ROUND((126720-sum(TIMESTAMPDIFF(MINUTE,Request_Date, REPLY_dATE)))/480) < 0 THEN 0 
ELSE LEAST(264, Round(SUM(TIMESTAMPDIFF(MINUTE,Request_Date, REPLY_dATE))/480))
END 

如果您还需要 WH 和 VH，您可以使用子查询来避免冗余:

SELECT VH, WH,
CASE 
  WHEN (SELECT VH) < 0 THEN 0 
  ELSE (SELECT WH)
END AS MyComputedColumn
FROM
(
  SELECT 
  ROUND((126720-sum(TIMESTAMPDIFF(MINUTE,Request_Date, REPLY_dATE)))/480) as VH,
  LEAST(264, Round(SUM(TIMESTAMPDIFF(MINUTE,Request_Date, REPLY_dATE))/480)) as WH
  FROM bbt2.new_table
) AS X;