sql - 如果字段长度大于 n，则自动递增计数器

Question

我被要求包含一个字段 ComNo，如果 Comments 的字段长度大于 5 个字符，Comment 中的其余字符应出现在下一行，并且 ComNo 字段应加 1

输入

EmpID EmpName ServiceNumber Date         Comments
1     a      123            23-03-1990   wednesday
1     a      1234           24-04-1990   Test12
2     b      234            24-05-2016   Todayis

预期的输出是

EmpID EmpName ServiceNumber Date       ComNo Comments
1     a       123           23-03-1990 1     wedne
1     a       123           23-03-1990 2     sday
1     a       1234          24-04-1990 1     Test1
1     a       1234          24-04-1990 2     2
2     b       234           24-05-2016 1     Today
2     b       234           24-05-2016 2     is

我有一个想法如何使用 plsql 过程来实现它，但我们可以使用 sql 查询来实现它吗？

Answer 1

这是一个不预先假设评论长度的解决方案。我添加了两个字符串，一个短(少于 5 个字符)和一个长于 10 个字符，以及一个带有 NULL 注释以确保该行不会丢失，以彻底测试解决方案。

我假设 (empid, dt)是基表中的唯一组合(可能是主键)。顺便说一句，我希望你实际上没有使用 Date作为架构中的列名。

解决方案不包含 WITH 子句；它始于 select empid... ORDER BY 子句也可能不需要。

with
     test_data ( empid, empname, servicenumber, dt, comments ) as (
       select 1, 'a',  123, to_date('23-03-1990', 'dd-mm-yyyy'), 'wednesday'   from dual union all
       select 1, 'a', 1234, to_date('24-04-1990', 'dd-mm-yyyy'), 'Test12'      from dual union all
       select 2, 'b',  234, to_date('24-05-2016', 'dd-mm-yyyy'), 'Todayis'     from dual union all
       select 2, 'b',  235, to_date('25-05-2016', 'dd-mm-yyyy'), 'Joe'         from dual union all
       select 3, 'c',  238, to_date('25-05-2016', 'dd-mm-yyyy'), ''            from dual union all
       select 4, 'c', 2238, to_date('25-05-2016', 'dd-mm-yyyy'), 'longer string' from dual
     )
select empid, empname, servicenumber, dt, level as comno,
       substr(comments, 5 * level - 4, 5) as comments
from   test_data
connect by level <= 1 + length(comments) / 5
     and   prior empid = empid
     and   prior dt    = dt
     and   prior sys_guid() is not null
order by empid, dt
;

     EMPID E SERVICENUMBER DT              COMNO COMMENTS
---------- - ------------- ---------- ---------- --------------------
         1 a           123 1990-03-23          1 wedne
         1 a           123 1990-03-23          2 sday
         1 a          1234 1990-04-24          1 Test1
         1 a          1234 1990-04-24          2 2
         2 b           234 2016-05-24          1 Today
         2 b           234 2016-05-24          2 is
         2 b           235 2016-05-25          1 Joe
         3 c           238 2016-05-25          1
         4 c          2238 2016-05-25          1 longe
         4 c          2238 2016-05-25          2 r str
         4 c          2238 2016-05-25          3 ing

已添加 : 如果你的数据是CLOB数据类型，最好使用 dbms_lob substr 的版本.此外，如果必须将数据切成 75 个字符段，则必须调整多个数字。这是相同的解决方案，有这两个更改并且没有 ORDER BY 子句(如果这用于将数据迁移到另一个数据库产品，则不需要)。注意: dbms_lob.substr()与通常的 substr() 相比，“金额”和“偏移量”(第二个和第三个参数)颠倒了功能;请注意，当您比较解决方案时。

select empid, empname, servicenumber, dt, level as comno,
       dbms_lob.substr(comments, 75, 75 * level - 74) as comments
from   test_data
connect by level <= 1 + length(comments) / 75
     and   prior empid = empid
     and   prior dt    = dt
     and   prior sys_guid() is not null
;