在mysql中删除除max(timestamp)之外的所有行

eit6fx6z 于 2021-06-20 发布在 Mysql

关注(0)|答案(5)|浏览(408)

如何删除除每天每种货币的最大值（时间戳）之外的所有行。因此，查询必须只保留每种货币每天的最新行。
数据

Id,currency,value,timestamp
21,btc,8000,2018-07-25 23:00:00 --> Keep this row
20,eth, 800,2018-07-25 23:00:00 --> Keep this row
19,btc,7999,2018-07-25 22:00:00
18,eth, 799,2018-07-25 22:00:00
17,btc,7998,2018-07-25 21:00:00
16,eth, 798,2018-07-25 21:00:00
15,btc,7997,2018-07-24 23:00:00 --> Keep this row
14,eth, 800,2018-07-24 23:00:00 --> Keep this row
13,btc,7996,2018-07-24 22:00:00
12,eth, 799,2018-07-24 22:00:00
11,btc,7995,2018-07-24 21:00:00
10,eth, 798,2018-07-24 21:00:00

每天每种货币的最新（最大（创建时间））行

SELECT
  t.currency
 , DATE(t.created_at)
 , MAX(t.created_at)

FROM `tbltest` t

GROUP BY 
   t.currency
 , DATE(t.created_at)

我可以找到每天每种货币的最新行（max（created_at）），但如何只保留这些行，并删除所有其他行？
解决方案
由于最高的时间戳也具有最高的id，因此使用的简单解决方案如下：

DELETE FROM `tbltest` 
WHERE id NOT IN 
    (SELECT MAX(id) 
       FROM (select * FROM `tbltest`) as t2 
   GROUP BY currency, DATE(created_at))

sql mysql

来源：https://stackoverflow.com/questions/51538556/delete-all-rows-except-maxtimestamp-on-each-day-in-mysql

5条答案

按热度按时间

xfb7svmp1#

一种方法是选择每个货币和日期的最大时间戳，并从删除中排除这些时间戳：

delete from tbltest
where (currency, created_at) not in
(
  select currency, max(created_at)
  from (select * from tbltest) t
  group by currency, date(created_at)
);

我认为这是非常可读的，在我看来，这在编写sql时非常重要。
线路

from (select * from tbltest) t

当然很奇怪。这是因为mysql拒绝您直接查询同一个表的更新。在其他dbms中，您只需编写 from tbltest 相反。
如果你更喜欢，你可以改变

where (currency, created_at) not in
(
  select currency, max(created_at)

到

where (currency, date(created_at), created_at) not in
(
  select currency, date(created_at), max(created_at)

当然，这也有同样的作用。选择您认为更可读/更直观的版本。

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

x6yk4ghg2#

具有最高时间戳的行似乎也具有最高的id。

DELETE FROM YourTable
WHERE Id NOT IN (SELECT MAX(Id) FROM YourTable GROUP BY currency, DATE(timestamp))

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

vlju58qv3#

你可以用rownumber来做这个把戏。这样地：

WITH CTE AS
(
  SELECT Currency,
         Timestamp,
         ROW_NUMBER() OVER (PARTITION BY Currency ORDER BY TIMESTAMP DESC) AS RowNumb
  FROM Table
)  

DELETE FROM CTE WHERE RowNumb > 1

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

xienkqul4#

您可以使用以下sql语句

Delete from tbltest
Where t.currency <>
(
  Select temp.currency
  From
  (
    SELECT Top 1 t.currency, DATE(t.created_at), MAX(t.created_at)
    FROM `tbltest` t
    GROUP BY t.currency, DATE(t.created_at)
    order by (max(created_at)) desc
  )temp
)

让我知道它是否有效！：）

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

bgibtngc5#

返回不删除行的子查询将帮助您：

DELETE FROM table_test
WHERE Id <> (SELECT last.id 
             FROM table_test last
             WHERE cast(last.created_at as date) = cast(table_test.created_at as date)
             ORDER BY last.created_at DESC
             LIMIT 1)

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

我来回答

在mysql中删除除max(timestamp)之外的所有行

5条答案

相关问题

热门标签

最新问答