SQL:从表A中删除记录,当id小于等于表B中一组中的最大id时。

huangapple go评论70阅读模式
英文:

SQL: delete records from the table A when id is less than equal to maximum id of a group in table B

问题

我需要删除arh表中的记录,对于每个accountMappingId,其中actionRequestId <= ar表中相应mappingId的"RESTORE"操作的最大actionRequestId。这些记录应仅在arh表中删除,不影响ar表。

已尝试的解决方案是:

delete from arh 
where (arh.actionRequestId, arh.accountMappingId) in (
select arh.actionRequestId, new_table.mappingId
from arh 
inner join (
  SELECT mappingId, max(actionrequestid) as actionrequestid
  FROM ar 
  where operation = 'RESTORE'
  group by mappingid
  ) 
as new_table
on arh.accountMappingId = new_table.mappingId
and arh.actionRequestId <= new_table.actionRequestId
order by arh.actionRequestId, new_table.mappingId)

是否可以优化删除查询?

英文:

I have 2 tables arh and ar .I need to delete records from arh table for each accountMappingId where actionRequestId is <= maximum actionRequestId for the operation 'RESTORE' on the equivalent mappingId in ar table. The records should be deleted only in arh table without affecting ar table.

There can be different accountMappingId (1001, 1002, 1003).

-- create table --

create table arh (
  accountMappingId TEXT,
  actionRequestId integer,
  actionrequesthistoryid integer
);

CREATE TABLE ar (
  mappingId TEXT,
  actionRequestId integer,
  operation TEXT
);

-- insert records ---

INSERT INTO arh VALUES (&#39;1001&#39;, 1, 1);
INSERT INTO arh VALUES (&#39;1001&#39;, 1, 2);
INSERT INTO arh VALUES (&#39;1001&#39;, 2, 1);
INSERT INTO arh VALUES (&#39;1001&#39;, 2, 2);
INSERT INTO arh VALUES (&#39;1001&#39;, 3, 1);
INSERT INTO arh VALUES (&#39;1001&#39;, 3, 2);
INSERT INTO arh VALUES (&#39;1001&#39;, 4, 1);
INSERT INTO arh VALUES (&#39;1001&#39;, 4, 2);
INSERT INTO arh VALUES (&#39;1001&#39;, 5, 1);
INSERT INTO arh VALUES (&#39;1001&#39;, 5, 2);
INSERT INTO arh VALUES (&#39;1001&#39;, 6, 1);
INSERT INTO arh VALUES (&#39;1001&#39;, 6, 2);
INSERT INTO arh VALUES (&#39;1001&#39;, 7, 1);
INSERT INTO arh VALUES (&#39;1001&#39;, 7, 2);
INSERT INTO arh VALUES (&#39;1001&#39;, 8, 1);
INSERT INTO arh VALUES (&#39;1001&#39;, 8, 2);
INSERT INTO arh VALUES (&#39;1002&#39;, 1, 1);
INSERT INTO arh VALUES (&#39;1002&#39;, 1, 2);
INSERT INTO arh VALUES (&#39;1002&#39;, 2, 1);
INSERT INTO arh VALUES (&#39;1002&#39;, 2, 2);
INSERT INTO arh VALUES (&#39;1002&#39;, 3, 1);
INSERT INTO arh VALUES (&#39;1002&#39;, 3, 2);

INSERT INTO ar VALUES (&#39;1001&#39;, 1, &#39;COPY&#39;);
INSERT INTO ar VALUES (&#39;1001&#39;, 2, &#39;REMOVE&#39;);
INSERT INTO ar VALUES (&#39;1001&#39;, 3, &#39;RESTORE&#39;);
INSERT INTO ar VALUES (&#39;1001&#39;, 4, &#39;COPY&#39;);
INSERT INTO ar VALUES (&#39;1001&#39;, 5, &#39;REMOVE&#39;);
INSERT INTO ar VALUES (&#39;1001&#39;, 6, &#39;RESTORE&#39;); --&gt; max(actionrequestId) for &#39;RESTORE&#39; operation in &#39;1001&#39;
INSERT INTO ar VALUES (&#39;1001&#39;, 7, &#39;COPY&#39;);
INSERT INTO ar VALUES (&#39;1001&#39;, 8, &#39;REMOVE&#39;);
INSERT INTO ar VALUES (&#39;1002&#39;, 1, &#39;COPY&#39;);
INSERT INTO ar VALUES (&#39;1002&#39;, 2, &#39;REMOVE&#39;);
INSERT INTO ar VALUES (&#39;1002&#39;, 3, &#39;RESTORE&#39;); --&gt; max(actionrequestId) for &#39;RESTORE&#39; operation in &#39;1002&#39;

Solution what I have tried but can we optimize the delete query?

delete from arh 
where (arh.actionRequestId, arh.accountMappingId) in (
select arh.actionRequestId, new_table.mappingId
from arh 
inner join (
  SELECT mappingId, max(actionrequestid) as actionrequestid
  FROM ar 
  where operation = &#39;RESTORE&#39;
  group by mappingid
  ) 
as new_table
on arh.accountMappingId = new_table.mappingId
and arh.actionRequestId &lt;= new_table.actionRequestId
order by arh.actionRequestId, new_table.mappingId)

答案1

得分: 2

使用子查询获取映射ID的最大还原请求ID。

DELETE FROM arh
WHERE actionRequestId <= (SELECT MAX(ar.actionRequestId)
                         FROM ar
                         WHERE ar.operation = 'RESTORE'
                         AND ar.mappingId = arh.accountMappingId);

演示链接: https://dbfiddle.uk/Frx1Ea3_

英文:

Use a subquery to get the Mapping ID's maximum RESTORE request ID.

DELETE FROM arh
WHERE actionRequestId &lt;= ( SELECT MAX(ar.actionRequestId) 
                           FROM ar
                           WHERE ar.operation = &#39;RESTORE&#39; 
                           AND ar.mappingId = arh.accountMappingId );

Demo: https://dbfiddle.uk/Frx1Ea3_

答案2

得分: 1

你可以从ar表中找到max(actionRequestId),条件是operation = 'RESTORE',然后使用USING子句从arh表中删除所需的行。

delete from arh T
using 
(
  select mappingId, max(actionRequestId) max_actionRequestId
  from ar
  where operation = 'RESTORE'
  group by mappingId
) D
where T.accountMappingId = D.mappingId and
   T.actionRequestId <= D.max_actionRequestId

查看示例

英文:

You could find the max(actionRequestId) from ar table where operation = &#39;RESTORE&#39;, then use the USING clause to delete the desired rows from arh table.

delete from arh T
using 
(
  select mappingId, max(actionRequestId) max_actionRequestId
  from ar
  where operation = &#39;RESTORE&#39;
  group by mappingId
) D
where T.accountMappingId = D.mappingId and
   T.actionRequestId &lt;= D.max_actionRequestId

See demo

huangapple
  • 本文由 发表于 2023年1月9日 14:21:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/75053773.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定