mysql联合排序_MySql完全连接(联合)和多个日期列的排序

一个相当复杂的SQL查询，我可能会变得更加困难，它应该是：

我有两个表格：

新闻：

newsid，datetime，newstext

图片：

图片，日期时间，imgPath

这两个不相关，我只是加入了创建新闻/图片的日期

迄今为止的SQL：

SELECT * FROM news as n LEFT OUTER JOIN (SELECT count(pictureid), datetime

FROM picture GROUP BY DATE(datetime)) as p ON DATE(n.datetime) = DATE(p.datetime)

UNION

SELECT * FROM news as n RIGHT OUTER JOIN (SELECT count(pictureid),

datetime FROM picture GROUP BY DATE(datetime)) as p ON

DATE(n.datetime) = DATE(p.datetime)我必须使用union来模拟MySQL中的完整外连接。

结果：

newsid text datetime count() datetime

1 sometext 2011-01-16 1 2011-01-16

2 moo2 2011-01-19 NULL NULL

3 mooo3 2011-01-19 NULL NULL

NULL NULL NULL 4 2011-01-14问题是，我显然最终会得到两个日期栏 - 一个来自新闻，另一个来自图片，这意味着我不能按日期排序并且按照正确的顺序排列！有任何想法吗？即使这意味着重构数据库！我需要约会在一个专栏。

答案来自SeRPRo

完成的工作代码是：

SELECT `newsid`, `text`,

CASE

WHEN `datetime` IS NULL

THEN `pdate`

ELSE `datetime`

END

as `datetime`,

`pcount` FROM

(

(SELECT * FROM news as n LEFT OUTER JOIN

(SELECT count(pictureid) as pcount, datetime as pdate FROM picture GROUP BY DATE(datetime)) as p

ON DATE(n.datetime) = DATE(p.pdate) ORDER BY datetime

)

UNION

(SELECT * FROM news as n RIGHT OUTER JOIN

(SELECT count(pictureid) as pcount, datetime as pdate FROM picture GROUP BY DATE(datetime)) as p

ON DATE(n.datetime) = DATE(p.pdate) ORDER BY datetime

)

) as x

ORDER BY datetime