mysql - 如何从我的查询中删除 row_num > 2 在 MySql 中？

Question

我的数据库中有这张表，其架构如下:

nearbystops(nearbystopname, latitude, longitude, mainaddress)，我做了如下查询

SELECT 
    nearbystopname,
    latitude, 
    longitude, 
    mainaddress, 
    ROW_NUMBER() OVER (
        PARTITION BY 
            nearbystopname,
            mainaddress
        ORDER BY 
            nearbystopname,
            mainaddress
    ) row_num
    FROM 
    DUSAgeocodeDB.nearbystops

这给了我以下内容:

我的问题是，如何删除 row_num > 2 的结果并将更改反射(reflect)到我的数据库中？

我试过下面的代码:

with cte as (
SELECT 
    nearbystopname,
    latitude, 
    longitude, 
    mainaddress, 
    ROW_NUMBER() OVER (
        PARTITION BY 
            nearbystopname,
            mainaddress
        ORDER BY 
            nearbystopname,
            mainaddress
    ) row_num
    FROM 
    DUSAgeocodeDB.nearbystops
    ) delete from cte where row_num > 1;

但它给了我以下错误:

Error Code: 1146. Table 'dusageocodedb.cte' doesn't exist

任何帮助将不胜感激!

Answer 1

假设你在表上有一个主键，那么:

delete ns 
    from DUSAgeocodeDB.nearbystops ns join
         (select nearbystopname, mainaddress, min(id) as min_id
          from DUSAgeocodeDB.nearbystops ns2
          group by nearbystopname, mainaddress
         ) ns2
         on ns2.nearbystopname = ns.nearbystopname and 
            ns2.mainaddress = ns.mainaddress and
            ns.id > ns.id;

如果您没有主键，最好的办法可能是重新填充表:

create table temp_ns as 
    select nearbystopname, latitude, longitude, mainaddress, 
           row_number() over (partition by nearbystopname, mainaddress) as seqnum
    from DUSAgeocodeDB.nearbystops ns;

truncate table DUSAgeocodeDB.nearbystops ns;  -- SAVE first!!!

insert into DUSAgeocodeDB.nearbystops (nearbystopname, latitude, longitude, mainaddress)
    select nearbystopname, latitude, longitude, mainaddress
    from temp_ns
    where seqnum = 1;