python - SQL Server：删除除最新的“n”个结果之外的所有结果

Question

import pyodbc
    
def operations():
    # server
    server = 'DESKTOP-3KK3D6005' 
    database = 'my_database'  
    sql_driver = '{ODBC Driver 17 for SQL Server}'

    # connection
    cnxn = pyodbc.connect(driver = sql_driver, 
                            server = server, 
                            database = database, 
                            trusted_connection ='yes')

    crsr = cnxn.cursor()

    # delete oldest rows to minimize overhead.
    crsr.execute("DELETE FROM my_table WHERE timestamp IN (SELECT timestamp FROM my_table ORDER BY timestamp ASC LIMIT 10)")

    # query streamed data
    maxval = crsr.execute("SELECT MAX(timestamp) FROM my_table").fetchval()
    print(maxval)

我只需要流式传输数据。我想删除除关于时间戳（纪元）的第 n 个最新行之外的所有内容，以最大限度地减少开销。

标签： pythonsqlsql-serverpython-3.xssms

Answer 1

你可以使用这个语法

删除最旧的 10

delete from my_table where timestamp in (
  select top 10 timestamp from my_table t order by timestamp
);

删除除 10 个最新的以外的所有内容

delete from my_table where timestamp not in (
  select top 10 timestamp from my_table t order by timestamp desc
);

或者，我们可以从前 N 个中找到最小时间戳并删除其余的

delete from my_table where timestamp < (
select min(timestamp) from 
 (select top 10 timestamp from my_table order by timestamp desc) m
);