php - MySQL联合查询，按2个变量排序

Question

每个表(table1 和 table2)都有自己的 DATETIME 字段。
我正在 try catch 两个表的 ID 并按它们的 DATETIME 字段对它们进行排序。

例子:

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

此查询的输出排序不正确:

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 

所需数据:
来自表 2 id: 2, 1,
来自表 1 id: 3, 2, 1
所以:2, 1, 3, 2, 1

////编辑

对于那些可能在长而复杂的 MySQL 请求中苦苦挣扎的人，请在 PhpmyAdmin 中尝试!它会告诉你错误!

////编辑

Answer 1

您真正需要做的是更仔细地考虑您的模式。考虑将日期时间列命名为相同，然后运行这样的查询 - 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

在 SQL fiddle 中，这会生成更接近您正在查找的结果。我仍然不确定为什么你需要在第二个查询中使用 INNER JOINS - 你在这里做的任何事情都不需要它们。

这是另一种方法，不需要更改列名，但需要可排序列的别名 - 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