order mysql查询结果按照另一个表列的desc顺序排列

edqdpe6u 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(323)

你好！
我的数据库里有两个表
表（订单）

+--------+----+---------+
|  name  | id | user_id |
+--------+----+---------+
| order1 | 1  | 3       |
| order2 | 2  | 5       |
| order3 | 3  | 5       |
| order4 | 4  | 1       |
| order5 | 5  | 5       |
+--------+----+---------+

表（报告的订单）

+----------+---------+------------+
| order_id | user_id | time       |
+----------+---------+------------+
| 2        | 5       | 1535110516 |
+----------+---------+------------+
| 3        | 5       | 1535166516 |
+----------+---------+------------+
| 5        | 5       | 1535168651 |
+----------+---------+------------+

当我执行这个查询时

SELECT name,id FROM orders 
WHERE 
user_id=5
AND 
id IN (SELECT order_id FROM reported_orders WHERE user_id=5 ORDER BY time DESC )
LIMIT 0, 12

我知道结果了

+--------+----+
|  name  | id |
+--------+----+
| order2 | 2  |
+--------+----+
| order3 | 3  |
+--------+----+
| order5 | 5  |
+--------+----+

但我想得到这样的结果

+--------+----+
|  name  | id |
+--------+----+
| order5 | 5  |
+--------+----+
| order3 | 3  |
+--------+----+
| order2 | 2  |
+--------+----+

我用 ORDER BY time DESC 在我的查询中，根据 time 在 reported_orders 但它不起作用

sql mysql sql-order-by

来源：https://stackoverflow.com/questions/52013770/order-mysql-query-results-in-desc-order-according-to-another-table-column

3条答案

按热度按时间

vq8itlhq1#

这个问题对我有用。

SELECT orders.name,orders.id, reported_orders.time
FROM orders
INNER JOIN reported_orders
ON (orders.id=reported_orders.order_id) 
WHERE 
(orders.user_id=5 AND reported_orders.user_id=5) 
ORDER BY time DESC

赞(0）回复(0）举报 2021-06-21

balp4ylt2#

尝试此查询

SELECT a.name,a.id FROM orders a join reported_orders b on a.id=b.id where a.user_id=5 and b.user_id=5 order by a.id desc

赞(0）回复(0）举报 2021-06-21

vvppvyoh3#

这对我有用。

select o.name, o.id from orders o natural join reported_orders ro order by ro.time desc;

赞(0）回复(0）举报 2021-06-20

我来回答

order mysql查询结果按照另一个表列的desc顺序排列

3条答案

相关问题

热门标签

最新问答