php - 查询 MySQL 和 FROM_UNIXTIME Epoch

Question

我在订购纪元字段时遇到问题，它不返回按日期排序的结果。然后我向您展示我的代码。

SELECT P.ID,
    MAX(CASE WHEN PM.meta_key = 'nombrem' THEN PM.meta_value END) AS nombrem,
    MAX(CASE WHEN PM.meta_key = 'generom' THEN PM.meta_value END) AS generom,
    MAX(CASE WHEN PM.meta_key = 'tiempom' THEN PM.meta_value END) AS tiempom,
    MAX(CASE WHEN PM.meta_key = 'urlm'    THEN PM.meta_value END) AS urlm,
    MAX(CASE WHEN PM.meta_key = 'imagenm' THEN PM.meta_value END) AS imagenm
FROM 
    K1nG_posts AS P
    LEFT JOIN K1nG_postmeta AS PM ON P.ID = PM.post_id 
WHERE 
    P.post_type = 'post' 
    AND (P.post_status = 'publish' OR P.post_status = 'private')  
GROUP BY P.ID, P.post_date ORDER BY FROM_UNIXTIME('tiempom')

“tiempom”字段的格式如下:

1570480237

我收到以下错误。

警告:#1292 错误的截断 DECIMAL 值:'tiempom'

Answer 1

你可以

GROUP BY P.ID, P.post_date ORDER BY FROM_UNIXTIME(tiempom)

或使用反引号`

GROUP BY P.ID, P.post_date ORDER BY FROM_UNIXTIME(`tiempom`)

因为 mysql 认为你想要将字符串 'tiempom' 转换为 datetime 。