您真正需要做的是更仔细地考虑您的架构。考虑将日期时间列命名为相同的名称,然后运行这样的查询-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提琴中,这将产生与您要查找的结果更接近的结果。我仍然不确定为什么您需要在第二个查询上使用INNER JOINS-在这里您不需要执行任何操作。
下面是不需要的列名的变化的另一种方法,但需要排序的列的别名-@L_301_2@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