我必须从oracle表中删除特定行。下面是表格结构:
-
对于
Item Id
1234,其中存在超过1行的Cancel状态,它应该删除Cancel状态的最后2行,这样只有两个记录保留在表中,其中02/Apr/2021到04/June/2021和05/June/2021到06/June/2021(也06/June/2021更新到31/Dec/3033)。我必须识别存在超过1个取消状态和一个活动状态的所有行,然后必须删除除1个取消行之外的所有取消行,然后必须将该行的项目结束日期更新为最大日期。 -
对于
Item Id
1235,由于只有一行退出Cancel状态,它应该从表中删除。我必须识别所有这样的行,其中状态只有取消,没有活动状态行存在的项目Id,然后删除它们。 -
对于
Item Id
1236,这是一个完美的状态,因此不应该发生这样的行。
项目结束日期
在SQL中确实是可能的。以下是对我有效的方法。下面的代码片段将保存active
状态下的项目id的详细信息:
create table active as
select *
from
(select id, state, count(*) as flg from itm group by id, state)
where state='Active';
类似地,在下面的代码片段中,为Cancel
状态的Item id创建一个表:
create table cancel as
select *
from
(select id, state, count(*) as flg from itm group by id, state)
where state='Cancel';
请注意,我已经创建了flg变量,分别保存active
和cancel
的记录数量。
下面的代码片段将暂时忽略完美的Item IDs
(即1236)。
create table target as
select a.id as a_id, a.state as a_state, a.flg as a_flg, c.*
from active as a
full join cancel as c
on a.id=c.id
where a.flg <> c.flg;
下面的代码将帮助我们获得需要分析的Item IDs
。
create table ads as select *, count(*) as cnt
from itm
where
id IN (select distinct a_id from target where a_id IS NOT NULL)
or
id IN (select distinct id from target where id IS NOT NULL)
group by id
order by id, state, start_date, end_date;
/*下面的代码段是用来删除任何只使用cancel */
的记录create table fin_ads as
select * from ads
where cnt <> 1;
下面的代码片段将给我们最终结果
/* The below snippet is to stitch records with multiple cancel */
select id, state, min(start_date) as start format=date9., max(end_date) as end format=date9.
from fin_ads
group by id, state
UNION
/* UNION is used to combine the perfect Item IDs back to the result */
select id, state, start_date as start, end_date as end from itm
where id IN
(
select distinct a.id
from active as a
inner join cancel as c
on a.id=c.id
where a.flg =c.flg)
我知道这不是一个优化的查询,很长。然而,这就得到了预期的结果。注意:使用子查询可以减少创建中间表的数量。希望这对你有帮助!请让我知道这是否有效/遇到任何问题。
您想要删除和更新表中的行还是仅仅从表中选择所描述的结果?
那么看起来表中每个项目总是最多有一个活动行。因此,您的结果显示每个状态各有一行,以及它们的最小开始日期和最大结束日期。
下面的简单查询可能已经做了你想做的事情:
select
item_id,
item_state,
min(item_start_date) as item_state_start_date,
max(item_end_date) as item_state_end_date
from mytable
group by item_id, item_state
order by item_id, item_state;
选择行。如果您想要删除和更新行,您可以简单地从上面的结果创建一个表,删除原始表中的行,并从新创建的表中填充表。(或者创建新表,删除旧表,重命名新表)