mysql联合排序,MySql完全联接（联合）和对多个日期列的排序

A rather complicated sql query that I might be making much more difficult that it should be:

I have two tables:

News:

newsid, datetime, newstext

Picture:

pictureid, datetime, imgPath

The two are not related, I am only joining by the date that the news/picture was created on

SQL so far:

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)

I have to use union to simulate a full outer join in MySQL.

The results:

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

The problem being that I obviously end up with two date columns- one from news and one from pictures, this means i cannot order by date and have it be in the correct order! Any ideas? Even if it means restructuring the database! I need date to be in a single column.

The answer came from SeRPRo

The completed working code is:

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

解决方案

just using your database structure and your query, and since FULL OUTER JOIN is not available in MySQL, I think a solution could be this:

SELECT

`newsid`,

`text`,

CASE

WHEN `datetime` IS NULL THEN `pdate`

ELSE `datetime`

END as `datetime,

`pcount`

(

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.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.datetime)

)