mysql order by timestamp_MySQL使用IN子句按时间戳排序(MySQL Order By timestamp using IN clause)...

MySQL使用IN子句按时间戳排序(MySQL Order By timestamp using IN clause)

我有以下查询，它可以根据设置的时间戳返回结果。

SELECT * FROM catalog WHERE part IN (SELECT part FROM query_data WHERE timestamp &t;= '2015-02-02') LIMIT 10

我想要做的是从DESC顺序获取上面的结果，但是ORDER BY时间戳。 像这样的东西，但它不起作用。 返回相同的值，但不是基于时间戳的DESC顺序。

SELECT * FROM catalog WHERE part IN (SELECT part FROM query_data WHERE timestamp >= '2015-02-02' ORDER BY timestamp DESC) LIMIT 10

思考？ 时间戳列只能在query_data表中找到。 不确定这是否导致问题？

I have the below query and it works fine at returning results based on the set timestamp.

SELECT * FROM catalog WHERE part IN (SELECT part FROM query_data WHERE timestamp >= '2015-02-02') LIMIT 10

What I would like to do is get the results from the above but ORDER BY timestamp in DESC order. Something like this, but it doesn't work. The same values are returned, but not in DESC order based on the timestamp.

SELECT * FROM catalog WHERE part IN (SELECT part FROM query_data WHERE timestamp >= '2015-02-02' ORDER BY timestamp DESC) LIMIT 10

Thoughts? The timestamp column is only found in the query_data table. Not sure if this is causing the issue or not?

原文：https://stackoverflow.com/questions/28281628

更新时间：2019-12-17 08:19

最满意答案

我相信这会奏效：

SELECT * FROM catalog c INNER JOIN query_data q ON c.part = q.part WHERE q.timestamp >= '2015-02-02' ORDER BY timestamp DESC;

您的方法的主要问题是您正在订购子查询。 使用连接和“order by”外部应该修复它。

I believe this will work:

SELECT * FROM catalog c INNER JOIN query_data q ON c.part = q.part WHERE q.timestamp >= '2015-02-02' ORDER BY timestamp DESC;

The main problem with your approach is that you are ordering the subquery. Using a join and "order by" outside should fix it.

2015-02-02

相关问答

我看到您的查询可能存在两个问题。 SELECT *

FROM dc_donations

ORDER BY DATE(dt) /* slow and wrong ! */

LIMIT 5

首先，您要存储一个完整的日期和时间，但是当您进行订购时，您只能按日期部分进行订购。 这就是DATE(dt)作用。 这会导致两个问题。 a)希望你在每个日历日都会有很多捐款，包括新到的捐款。 SQL排序的工作方式，如果您没有完全指定订单，则允许服务器无法预测(随机)订购商品。 因此，您的查询

...

事实证明，如果我把.Generated.Never()放在timestamp属性生成从MySQL转移到NHibernate，所以问题就消失了。 但是，如果可能的话，我更喜欢它在MySQL端。 Turns out if I put .Generated.Never() on the timestamp property generation moves to NHibernate from MySQL and so the issue disappears. However, I prefer it

...

如果你不介意重复id序列，使用ORDER BY FIELD应该可以做到这一点： SELECT * FROM table WHERE id IN(3245,76,3466,998,12984,4466,931,50,728)

ORDER BY FIELD (id,3245,76,3466,998,12984,4466,931,50,728)

正如@vyegorov在评论中指出的那样，对于大量的id，你可能需要创建一个临时表来保存实际的顺序 CREATE TEMPORARY TABLE `temp_

...

只需删除 $row = mysql_fetch_array($sql);

...吞下你的第一个结果 Just remove $row = mysql_fetch_array($sql);

...which is swallowing your first result

我相信这会奏效： SELECT * FROM catalog c INNER JOIN query_data q ON c.part = q.part WHERE q.timestamp >= '2015-02-02' ORDER BY timestamp DESC; 您的方法的主要问题是您正在订购子查询。 使用连接和“order by”外部应该修复它。 I believe this will work: SELECT * FROM catalog c INNER JOIN query_data

...

您在没有价值。 你需要这样的东西： $options = $options." $row[0] ";

You do not have value in . You need something like this: $options = $options." $row[0] ";

SELECT name

FROM tbl1

ORDER BY sequence = 0,

sequence ASC

要么 SELECT name

FROM tbl1

ORDER BY case when sequence <> 0 then 1 else 2 end,

sequence ASC

SELECT name

FROM tbl1

ORDER BY sequence = 0,

sequence ASC

or SELECT na

...

问题是您的时间戳的字符串表示形式不是规范格式，也就是说，排序字符串值不按时间戳顺序排序。 要按顺序排序行，可以将值的字符表示形式转换为DATETIME或TIMESTAMP数据类型，或者至少转换为规范格式的字符表示形式(例如'YYYY-MM-DD hh：mm：ss'with 24小时制)。 STR_TO_DATE函数用于将已知格式的字符串表示转换为DATETIME： SELECT * FROM table

ORDER BY STR_TO_DATE(`timestamp`,'%m/%d/%Y %h:%

...

下面，对我有用... 假设... //...note, date is in ISO format...

LocalDateTime ldt = LocalDateTime.parse(tmstmpString, DateTimeFormatter.ISO_DATE_TIME);

Timestamp tmstmp = Timestamp.valueOf(ldt);

这是一种方式...... Query query = entityManager.createNat

...

您可以将查询包装在子查询中，并在外部查询中执行过滤： SET @day := 0, @id:= '';

SELECT day, id, day_number

FROM (

SELECT

day, id,

@day := IF(@id = id, @day + 1,

IF(@id := id, 1, 1)) as day_number

FROM

myTable

WHERE

month = E

...