DATE() 类型字段在 JOIN 查询中显示为 0000-00-00

Question

执行JOIN查询时，MySQL为DATE((类型字段输出0000-00-00，尽管在执行简单的SELECT(2017-03-02等(时值不为空。

SELECT * FROM t1
JOIN t1_t2 ON t1_t2.t1_id = t1.t1_id
JOIN t2 ON t1_t2.t2_id = t2.t2_id
WHERE t1_id = 52
ORDER BY t2.type, t1.my_date_field;

奇怪的是，当我在查询中添加DATE_FORMAT(my_date_field(时，它可以工作：

SELECT *, DATE_FORMAT(`my_date_field`, "%Y-%m-%d")
(...)

我做了一个SQLFiddle http://sqlfiddle.com/#!9/23a965/8 但无法复制该问题

编辑 问题来自 ORDER BY 子句，删除"ORDER BY release_date"解决了"问题 - 但仍然没有在小提琴上复制。

Answer 1

考虑到您的问题听起来像是 mysql 错误，我有两个选择供您尝试。

选项 1

SELECT *, DATE_FORMAT(alb.release_date, "%Y-%m-%d")
FROM artist art
JOIN track_artist_album taa  ON taa.artist_id = art.id
JOIN album alb ON alb.id = taa.album_id
JOIN track tra ON tra.id = taa.track_id
WHERE art.id = 1
ORDER BY tra.name, DATE_FORMAT(alb.release_date, "%Y-%m-%d");

选项 2：

SELECT * FROM 
(
SELECT art.id as art_id, art.name as art_name, 
  alb.id as alb_id, alb.name as album_name, tra.id as tra_id, tra.name as tra_name, DATE_FORMAT(alb.release_date, "%Y-%m-%d") fmt_release_date
FROM artist art
JOIN track_artist_album taa  ON taa.artist_id = art.id
JOIN album alb ON alb.id = taa.album_id
JOIN track tra ON tra.id = taa.track_id
WHERE art.id = 1
) A
ORDER BY a.tra_name, a.fmt_release_date