我在表A中有双精度或更多的数据。我想要获取最后输入的数据并删除旧数据。多么?
我尝试使用select with distinc和inner join,但在execute时删除旧的和最后的数据是include。所以我有一些问题。
Select * from A where po in (select max(po) from B)
结果是数据无效。
发布于 2019-06-21 09:09:44
首先,我从distinc
表A生成新的表结果。
create table c AS
select distinct po, plan_ref from A group by po, plan_ref
After that, copy the table A. Let say it begin table B.
delete all the data in table A.
`INSERT INTO A (id, po
, plan_ref
, cust_order
, cust_code
, cust_name
, destination
, art_no
, art_name
, cust_reqdate
, posdd
, podd
, ship_date
, container
, ship
, plant_cell
, cbm
, remark
, upload_by
, upload_date)
select MAX(a.id), MAX (a.po)
, MAX(a.plan_ref)
, MAX(a.cust_order)
, MAX(a.cust_code)
, MAX(a.cust_name)
, MAX(a.destination)
, MAX(a.art_no)
, MAX(a.art_name)
, MAX(a.cust_reqdate)
, MAX(a.posdd)
, MAX(a.podd)
, MAX(a.ship_date)
, MAX(a.container)
, MAX(a.ship)
, MAX(a.plant_cell)
, MAX(a.cbm)
, MAX(a.remark)
, MAX(a.upload_by)
, MAX(a.upload_date) from C b
inner join B a on a.plan_ref = b.plan_ref
AND a.po = b.po GROUP BY a.po`
发布于 2019-06-21 10:56:01
如果您想要删除除最大po
之外的所有内容(如您的代码所建议的),那么您可以这样做:
delete from a
where po < (select max(a2.po) from a a2);
更常见的情况是,您可能希望保持基于其他列的最大po
。为此,使用相关子查询:
delete from a
where po < (select max(a2.po) from a a2 where a2.? = a.?); -- ? is for the grouping column
https://stackoverflow.com/questions/56695613
复制相似问题