logo标签列表

使用Oracle SQL查找逗号分隔字符串中的最大数值

sqloracleplsqloracle10gaggregate-functions
3

我有一个包含两列的表格：

OLD_REVISIONS   |NEW_REVISIONS
-----------------------------------
1,25,26,24      |1,26,24,25
1,56,55,54      |1,55,54
1               |1
1,2             |1
1,96,95,94      |1,96,94,95
1               |1
1               |1
1               |1
1               |1
1,2             |1,2
1               |1
1               |1
1               |1
1               |1

  • 每行都将列出一个文档的修订版本列表（以逗号分隔）

  • 逗号分隔的列表可能在两列中相同，但顺序/排序可能不同 - 例如：

    2,1 |1,2

我想找到所有旧版修订版本列中最高版本低于新版修订版本列中最高版本的实例。

以下情况符合此标准：

OLD_REVISIONS   |NEW_REVISIONS
-----------------------------------
1,2             |1
1,56,55,54      |1,55,54

  • 我已经尝试使用MINUS选项（将表格连接到自身），但即使列表相同但顺序错误时，它也会返回差异。

  • 我尝试了GREATEST函数（例如greatest(new_Revisions) < greatest(old_revisions)），但不确定为什么greatest(OLD_REVISIONS)总是只返回逗号分隔的值。 它没有返回最大值。 我怀疑它正在比较字符串，因为这些列是VARCHAR。

此外，MAX函数期望单个数字。

有没有其他方法可以实现上述目标？ 我正在寻找纯SQL选项，以便我可以打印出结果（或者是可以打印出结果的PL / SQL选项）

编辑

抱歉我没有提到，但对于NEW_REVISIONS，我实际上的确在一个表格中拥有每个修订版本都在单独的行中的数据：

"DOCNUMBER" "REVISIONNUMBER"
67          1
67          24
67          25
67          26
75          1
75          54
75          55
75          56
78          1
79          1
79          2
83          1
83          96
83          94

为了提供一些内容，几周前我怀疑有修订版本消失了。 为了调查这件事，我决定对所有文档的所有修订进行计数并拍摄快照以便稍后比较，看是否确实丢失了修订版本。
我拍摄的快照包含以下列：
docnumber、count、revisions
修订版本使用listagg函数以逗号分隔的列表存储。
我现在遇到的问题是，在实时表中添加了新的修订版本，因此当我使用MINUS比较主表和快照表时，由于主表中的新修订版本，我会得到一个差异。
即使在实际表中修订版本是单独的行，在快照表中我也没有单独的行。
我想唯一的重建快照的方式是以相同的格式，并比较它们是否找到主表中的最大修订版本是否低于快照表中的最大修订版本（因此我正在尝试找出如何在逗号分隔的字符串中找到最大值）。
5
这种方式表达价值观是错误的。每个修订版本应该有一行，而不是将大量修订版本塞入一个字符串中。 - Gordon Linoff
4
如果您正确地对数据模型进行规范化，查询将非常简单。 - user330315
2
将这个表分成两个表（旧的和新的），同时您还需要将逗号分隔的值拆分为行。之后就很简单了。 - PeterRing
1
你尝试的一切都失败了，很简单的原因是 - 你当前表格中的是字符串；它们由逗号分隔的数字无关紧要。你所做的任何比较(包括用max()或greatest())都只会将它们作为字符串进行比较。你需要将字符串拆分成它们的组成数字。 - user5683823
greatest(stringA, stringB) 将按照排序顺序返回两个字符串中的较大值。无论这两个字符串是否包含逗号，都不会影响结果，我不确定为什么有人会期望逗号会有所影响。 - William Robertson
显示剩余2条评论
7个回答
4

希望你享受。

select    xmlcast(xmlquery(('max((' || OLD_REVISIONS || '))') RETURNING CONTENT) as int) as OLD_REVISIONS_max
         ,xmlcast(xmlquery(('max((' || NEW_REVISIONS || '))') RETURNING CONTENT) as int) as NEW_REVISIONS_max

from      t
;
@ziggy，请注意我添加了 xmlcast 函数。在 Oracle 10g 上能用吗？ - David דודו Markovitz
2
假设您的基础表有一个id列（哪个版本？）- 这里提供一种基于拆分行的解决方案。
编辑：如果您喜欢这个解决方案，请查看vkp的解决方案，比我的更好。我在他的答案评论中解释了他的解决方案为什么更好。
with
     t ( id, old_revisions, new_revisions ) as (
       select 101, '1,25,26,24', '1,26,24,25' from dual union all
       select 102, '1,56,55,54', '1,55,54'    from dual union all
       select 103, '1'         , '1'          from dual union all
       select 104, '1,2'       , '1'          from dual union all
       select 105, '1,96,95,94', '1,96,94,95' from dual union all
       select 106, '1'         , '1'          from dual union all
       select 107, '1'         , '1'          from dual union all
       select 108, '1'         , '1'          from dual union all
       select 109, '1'         , '1'          from dual union all
       select 110, '1,2'       , '1,2'        from dual union all
       select 111, '1'         , '1'          from dual union all
       select 112, '1'         , '1'          from dual union all
       select 113, '1'         , '1'          from dual union all
       select 114, '1'         , '1'          from dual
       )
--   END of TEST DATA; the actual solution (SQL query) begins below.
select id, old_revisions, new_revisions
from (
    select id, old_revisions, new_revisions, 'old' as flag,
           to_number(regexp_substr(old_revisions, '\d+', 1, level)) as rev_no
      from t
      connect by level <= regexp_count(old_revisions, ',') + 1
         and  prior id = id
          and prior sys_guid() is not null
    union all
    select id, old_revisions, new_revisions, 'new' as flag,
           to_number(regexp_substr(new_revisions, '\d+', 1, level)) as rev_no
      from t
      connect by level <= regexp_count(new_revisions, ',') + 1
         and  prior id = id
          and prior sys_guid() is not null
     )
group by id, old_revisions, new_revisions
having max(case when flag = 'old' then rev_no end) !=
       max(case when flag = 'new' then rev_no end)
order by id           --   ORDER BY is optional
;


 ID OLD_REVISION NEW_REVISION
--- ------------ ------------
102 1,56,55,54   1,55,54   
104 1,2          1
1
一种方法是使用regexp_substr将列按逗号分隔，并检查最大值和最小值是否不同。

示例演示

with rownums as (select t.*,row_number() over(order by old_revisions) rn from t)
select old_revisions,new_revisions 
from rownums 
where rn in (select rn
             from rownums
             group by rn
             connect by regexp_substr(old_revisions, '[^,]+', 1, level) is not null 
             or regexp_substr(new_revisions, '[^,]+', 1, level) is not null
             having max(cast(regexp_substr(old_revisions,'[^,]+', 1, level) as int)) 
              <> max(cast(regexp_substr(new_revisions,'[^,]+', 1, level) as int))
    )
好主意！拆分只需要在HAVING子句中进行，而不是其他任何地方。因此，最好将拆分移到HAVING子句中，在GROUP BY评估之后才进行评估。因此，您不需要担心prior rn = rn和避免层次查询中的循环。记住这个好技巧-致敬并点赞！ - user5683823
1

评论中提到需要将数据规范化，我同意这个观点，但也理解这可能不太容易。我会尝试使用以下查询语句：

select greatest(val1, val2), t1.r from (
select max(val) val1, r from (
select regexp_substr(v1,'[^,]+', 1, level) val, rowid r from tab1
  connect by regexp_substr(v1, '[^,]+', 1, level) is not null
  ) group by r) t1
  inner join (
select max(val) val2, r from (
select regexp_substr(v2,'[^,]+', 1, level) val, rowid r from tab1
  connect by regexp_substr(v2, '[^,]+', 1, level) is not null
  ) group by r) t2
  on (t1.r = t2.r);

测试环境：

create table tab1 (v1 varchar2(100), v2 varchar2(100));
insert into tab1 values ('1,3,5','1,4,7');
insert into tab1 values ('1,3,5','1,2,9');
insert into tab1 values ('1,3,5','1,3,5');
insert into tab1 values ('1,3,5','1,4');

看起来正常运行。我为参考留下了rowid。我猜你的表里有一些id。

在你进行编辑之后，我会将查询更改为：

select greatest(val1, val2), t1.r from (
select max(val) val1, r from (
select regexp_substr(v1,'[^,]+', 1, level) val, DOCNUMBER r from tab1
  connect by regexp_substr(v1, '[^,]+', 1, level) is not null
  ) group by DOCNUMBER) t1
  inner join (
select max(DOCNUMBER) val2, DOCNUMBER r from NEW_REVISIONS) t2
  on (t1.r = t2.r);
1
你可以使用listagg函数将相同顺序的修订版本组合在一起，以比较每个值。
SELECT listagg(o,',') WITHIN GROUP (ORDER BY o) old_revisions,
       listagg(n,',')  WITHIN GROUP (ORDER BY n) new_revisions
FROM (
     SELECT DISTINCT  rowid r,
            regexp_substr(old_revisions, '[^,]+', 1, LEVEL) o,
            regexp_substr(new_revisions, '[^,]+', 1, LEVEL) n
     FROM   table
     WHERE  regexp_substr(old_revisions, '[^,]+', 1, LEVEL) IS NOT NULL
     CONNECT BY LEVEL<=(SELECT greatest(MAX(regexp_count(old_revisions,',')),MAX(regexp_count(new_revisions,',')))+1 c FROM table)
     )
GROUP BY r
HAVING listagg(o,',') WITHIN GROUP (ORDER BY o)<>listagg(n,',') WITHIN GROUP (ORDER BY n);
1
这可能是一种方法：

select 
  OLD_REVISIONS,
  NEW_REVISIONS
from 
  REVISIONS t,
  table(cast(multiset(
                        select level
                        from dual
                        connect by  level <= length (regexp_replace(t.OLD_REVISIONS, '[^,]+'))  + 1
                      ) as sys.OdciNumberList
             )
       ) levels_old,
  table(cast(multiset(
                        select level
                        from dual
                        connect by  level <= length (regexp_replace(t.NEW_REVISIONS, '[^,]+'))  + 1
                     )as sys.OdciNumberList
            )
       ) levels_new
group by t.ROWID,
  OLD_REVISIONS,
  NEW_REVISIONS
having max(to_number(trim(regexp_substr(t.OLD_REVISIONS, '[^,]+', 1, levels_old.column_value)))) >
       max(to_number(trim(regexp_substr(t.new_REVISIONS, '[^,]+', 1, levels_new.column_value))))

这里使用了双重字符串分割来获取每个字段的值，然后找到两个集合中最大值符合要求的行。 如果您的表没有唯一键，则应通过在GROUP BY子句中添加某些唯一键或行ID来编辑此内容。请注意保留HTML标签。
0
您可以编写一个 PL/SQL 函数来解析字符串并返回最大数。
select  max_num( '1,26,24,25') max_num from dual;
   MAX_NUM
----------
        26

查询非常简单：

select OLD_REVISIONS  NEW_REVISIONS
from revs
where max_num(OLD_REVISIONS) < max_num(NEW_REVISIONS);

一个没有验证和错误处理的原型函数

create or replace function max_num(str_in VARCHAR2) return NUMBER as 
i number;
x varchar2(1);
n number := 0;
max_n number := 0;
pow number := 0;
begin
 for i in 0.. length(str_in)-1 loop
  x := substr(str_in,length(str_in)-i,1);
  if x = ',' then 
    -- check max number
    if n > max_n then 
       max_n := n;
    end if;   
    -- reset
    n := 0;
    pow := 0;
  else
    n := n + to_number(x)*power(10,pow);
    pow := pow +1;
  end if;
 end loop;
 return(max_n);
end;
/
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接