mysql 联合查询后排序,MySQL联合查询，按2个变量排序

Each table (table1 & table2) has its own DATETIME field.

I'm trying to catch id's of the two tables and order them by their DATETIME field.

Example:

Table 1 Table 2

------------ -------------

id | datetime1 id | table1id | datetime2

------------------------ -----------------------

1 | 2014-09-21 20:31:26 1 | 2 | 2014-09-21 20:31:29

2 | 2014-09-21 20:31:27 2 | 3 | 2014-09-21 20:31:30

3 | 2014-09-21 20:31:28

Table 3

------------

id | user

------------------------

2 | phil

3 | nathalie

My output isn't ordered properly with this query:

SELECT *

FROM (

SELECT

1 AS selection,

table1.id, table1.datetime1,

table2.datetime2

table3.user

FROM Table1

LEFT OUTER JOIN table2

ON table1.id = table2.table1id

LEFT OUTER JOIN table3

ON table1.id = table3.id

UNION ALL

SELECT

2 AS selection,

table1.id, table1.datetime1,

table2.datetime2

table3.user

FROM Table1

INNER JOIN table2

ON table1.id = table2.table1id

INNER JOIN table3

ON table1.id = table3.id

) AS query

ORDER BY table1.datetime1 DESC, table2.datetime2 DESC

Desired data:

from table 2 id: 2, 1,

from table 1 id: 3, 2, 1

So: 2, 1, 3, 2, 1

EDIT

To people who could be struggling with long and complex MySQL request, please try it in PhpmyAdmin! It will tell you the error!

EDIT

解决方案

What you really need to do is to consider your schema more carefully. Consider naming the date time columns the same and then running a query like this - http://sqlfiddle.com/#!2/a3b4c/7/0

SELECT selection, id, datetimefoo, user FROM (

SELECT

1 AS selection,

table1.id, table1.datetimefoo,

table3.user

FROM table1

LEFT OUTER JOIN table2

ON table1.id = table2.table1id

LEFT OUTER JOIN table3

ON table1.id = table3.id

UNION

SELECT

2 AS selection,

table1.id, table1.datetimefoo,

table3.user

FROM table1

INNER JOIN table2

ON table1.id = table2.table1id

INNER JOIN table3

ON table1.id = table3.id

) AS T2

ORDER BY datetimefoo DESC

In the SQL fiddle this produces the results closer to what you're looking for. I am still not sure why you need the INNER JOINS on the second query though - there is nothing that you're doing here whcih requires them.

Here is another method that does not require a changing of the column names, but requires an alias for the sortable columns - http://sqlfiddle.com/#!2/ec4bc/3/0

SELECT * FROM (

SELECT

1 AS selection,

table1.id, table1.datetimefoo AS sort_date, -- alias on first table's date

table2.datetimebar,

table3.user

FROM table1

LEFT OUTER JOIN table2

ON table1.id = table2.table1id

LEFT OUTER JOIN table3

ON table1.id = table3.id

UNION

SELECT

2 AS selection,

table1.id, table1.datetimefoo,

table2.datetimebar AS sort_date, -- alias on second table's date

table3.user

FROM table1

INNER JOIN table2

ON table1.id = table2.table1id

INNER JOIN table3

ON table1.id = table3.id

) AS T2

ORDER BY sort_date DESC