MYSQL为最后5个不同的记录选择5个记录

MYSQL为最后5个不同的记录选择5个记录

在许多其他DBMS（Oracle，SQL-Server，Postgres）中，可以使用窗口函数：

SELECt id, file, folder, addedFROM  ( SELECt id, file, folder, added,DENSE_RANK() OVER (ORDER BY added DESC) AS d_rank,ROW_NUMBER() OVER (PARTITION BY added ORDER BY id DESC) AS row_no    FROM AviationImages  ) dWHERe d_rank <= 5          -- limit number of dates  AND row_no <= 5 ;        -- limit number of images per date

在MySQL中，您没有窗口函数和

OVER

子句的奢侈：

SELECt i.id, i.file, i.folder, i.addedFROM    ( SELECt DISTINCT added      FROM AviationImages      ORDER BY added DESC      LIMIT 5    ) AS da  JOIN    AviationImages AS i      ON  i.added = da.added      AND i.id >= COALESCE(          ( SELECt ti.id FROM AviationImages AS ti WHERe ti.added = da.added ORDER BY ti.id DESC LIMIT 1 OFFSET 4          ), -2147483647) ;  -- use 0 if the `id` is unsigned int

索引打开

(added, id)

将有助于提高效率-如果表使用InnoDB并且

id

为主键，则仅索引打开

(added)

就足够了。