我正在尝试编写一个delete查询,根据日期从数据库中删除旧记录,但问题是同一天可能有多个记录,我只需要保留最近的记录。
下面是我的问题:
SELECT
ItemName,
1_PRICE,
1_STOCK,
2_STOCK,
updated
FROM
inventory
WHERE
ItemName = "24T7351"
ORDER BY
updated DESC这是SQLFiddle:http://sqlfiddle.com/#!9/fb0a3d/1
因此,基本上我想要的是一个查询,它返回按天分组的所有记录,除了每天的最新记录。
伪代码: select * group by updated order by desc保留每一天的第一行,删除每一天的其余行
发布于 2018-02-10 12:13:24
这样如何:
DELETE FROM inventory
WHERE updated NOT IN (
SELECT updated FROM (
SELECT MAX(updated) updated
FROM inventory
GROUP BY DATE(updated)
) i
)如果您对updated进行了索引(排序),这将非常有效。
基本上,子查询获取每天的所有最大更新日期,并从DELETE语句中排除它们(NOT IN)。
发布于 2018-02-10 11:48:35
获取子查询中的最新时间,将其与表连接,然后删除。
DELETE i1
FROM inventory AS i1
JOIN (SELECT DATE(updated) AS date, MAX(updated) AS latest
FROM inventory
WHERE itemname = '24T7351'
GROUP BY date) AS i2 ON DATE(i1.updated) = i2.date AND i1.updated != i2.latest
WHERE itemname = '24T7351'发布于 2018-02-10 12:01:16
如果您想要每个项目的每个日期的最新记录:
SELECT i.*
FROM inventory i
WHERE i.updated < (select max(i2.updated)
from inventory i2
where i2.itemname = i.itemname and
date(i2.updated) = date(i.updated)
)
ORDER BY updated DESC;https://stackoverflow.com/questions/48716986
复制相似问题