mysql中用timest_mysql – 我可以在TIMESTAMPDIFF中使用字段作为...

TIMESTAMPDIFF()不支持这样的动态单位.

由于您使用的是一组已知单位,因此可以使用CASE语句来实现此目的.

从您的示例查询开始,这样的事情可能会起作用：

SELECT foo

FROM table t

LEFT JOIN frequencies f USING (frequency_id)

WHERE MOD(

(CASE

WHEN f.frequency_code = 'MICROSECOND' THEN TIMESTAMPDIFF(MICROSECOND, t.date, NOW())

WHEN f.frequency_code = 'SECOND' THEN TIMESTAMPDIFF(SECOND, t.date, NOW())

WHEN f.frequency_code = 'MINUTE' THEN TIMESTAMPDIFF(MINUTE, t.date, NOW())

WHEN f.frequency_code = 'HOUR' THEN TIMESTAMPDIFF(HOUR, t.date, NOW())

WHEN f.frequency_code = 'DAY' THEN TIMESTAMPDIFF(DAY, t.date, NOW())

WHEN f.frequency_code = 'WEEK' THEN TIMESTAMPDIFF(WEEK, t.date, NOW())

WHEN f.frequency_code = 'MONTH' THEN TIMESTAMPDIFF(MONTH, t.date, NOW())

WHEN f.frequency_code = 'QUARTER' THEN TIMESTAMPDIFF(QUARTER, t.date, NOW())

WHEN f.frequency_code = 'YEAR' THEN TIMESTAMPDIFF(YEAR, t.date, NOW())

END)

, t.frequency_num) = 0