MySQL联合查询,按2个变量排序 [英] MySQL union query, order by 2 variables
问题描述
每个表(表1和表2)都有自己的DATETIME字段.
我正在尝试捕获两个表的ID,并按其DATETIME字段对其进行排序.
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.
示例:
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
所需数据:
从表2 id:2,1,
来自表1 id:3、2、1
所以:2、1、3、2、1
Desired data:
from table 2 id: 2, 1,
from table 1 id: 3, 2, 1
So: 2, 1, 3, 2, 1
////编辑
对于可能要处理冗长而复杂的MySQL请求的人,请在PhpmyAdmin中尝试一下!它会告诉您错误!
To people who could be struggling with long and complex MySQL request, please try it in PhpmyAdmin! It will tell you the error!
////编辑
推荐答案
您真正需要做的是更仔细地考虑架构.考虑将日期时间列命名为相同的名称,然后像这样运行查询- http://sqlfiddle .com/#!2/a3b4c/7/0
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
在SQL提琴中,这将产生与您要查找的结果更接近的结果.我仍然不确定为什么在第二个查询中需要使用INNER JOINS -在这里您不需要执行任何操作.
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.
这里是另一种方法,不需要更改列名,但需要可排序列的别名- http://sqlfiddle.com/#!2/ec4bc/3/0
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
这篇关于MySQL联合查询,按2个变量排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!