在 SQL 语句中查找字符串中的字符串

Question

我有一栏显示我们公司经历过的项目的标题，另一栏显示每个项目的工作时间。

项目标题包含关键字，关键字由格式“关键字：”定义，即“ETL：”

有些项目有多个关键字，即“客户：ETL：ASCX：”

因此，例如，项目标题可以是“客户：ETL：ASCX” ：更新导入流程'

我提前不知道关键字。

我想要给定关键字的总小时数和项目数 因此，我们以以下两个项目标题为例：

• 客户：ETL：ASCX：已工作 20 小时 投入的工作。
• 客户：ETL： 桥梁：投入了10个小时的工作 它。

报告应该给出：

Keyword - Total Projects - Total Hours
Client: - 2 -30
ETL: - 2 - 30
ASCX: - 1 - 20
Bridge: - 1 - 10

获取关键字的第一个实例很容易 - 只需子字符串；但事实证明找到嵌套关键字很困难。

可以在 SQL 中进行嵌套搜索吗？

有接受者吗？

更新（最初作为“答案”发布）：

进一步的示例：

假设我有两条具有以下项目标题的记录：

Record 1: Interface: ETL:
Record 2: ETL:

记录 1 有 10 小时，记录 2 有 30 小时。

现在，我的代码捕获第一个关键字实例，因此我现在的输出是 (关键字：小时)

ETL: 30
Interface: 10

但是，要求是显示 ETL 已分配 40 小时，因为两个项目将 ETL 作为关键字：

ETL: 40
Interface: 10

所以当然，我可以使用 LIKE 查找 ETL 或接口的所有实例，但我需要在选择每个关键字时进行细分。在上面的示例中，如果我使用类似“％ETL：％”的内容，我将获得两条记录，但我想查看所有关键字的所有时间，并按关键字细分。

也许更好的问题是：

如何获得如下所示的记录：

Interface: ETL:

进入如下所示的输出：

Interface:
ETL:

在 sql 中？

Answer 1

不是很漂亮，但您可以使用 MODEL 子句来进行分割（只是一种方式）。假设您有单独的 project 和 project_hours 表，通过 ID 字段建立关系：

create table projects as (
    select 'Client: ETL: ASCX: update the import process' as project_title,
        1 as project_id from dual
    union all select 'Client: ETL: Bridge: something else', 2 from dual
    --union all select 'Interface: ETL:', 3 from dual
    --union all select 'ETL:', 4 from dual
)
/

create table project_hours as (
    select 1 as project_id, 20 as hours from dual
    union all select 2, 10 from dual
    --union all select 3, 10 from dual
    --union all select 4, 30 from dual
)
/

此：

with tmp_tab as (
    select project_id, trim(t) as keyword, i
        from projects
        model
        return updated rows
        partition by (project_id)
        dimension by (0 i)
        measures (project_title t)
        rules (t[for i from 1 to
                (length(regexp_replace(':' || t[0],'[^:]')) - 1) increment 1]
            = regexp_substr(t[0],'[^:]+',1,cv(i)))
    order by project_id, i
)
select tt.keyword,
    count(distinct tt.project_id) as total_projects,
    sum(h.hours) as total_hours
from tmp_tab tt
left join project_hours h on h.project_id = tt.project_id
group by tt.keyword
/

给出此：

KEYWORD                          TOTAL_PROJECTS     TOTAL_HOURS        
-------------------------------- ------------------ ------------------ 
Bridge                           1                  10                 
ETL                              2                  30                 
Client                           2                  30                 
ASCX                             1                  20                 

编辑 或者如果您的第二组示例是包含，给出：

KEYWORD                          TOTAL_PROJECTS     TOTAL_HOURS        
-------------------------------- ------------------ ------------------ 
ETL                              4                  70                 
Bridge                           1                  10                 
Interface                        1                  10                 
Client                           2                  30                 
ASCX                             1                  20                 

改编自答案 这里 - 所以任何功劳都应该归于Rob van Wijk。

我假设关键字后面总是跟着一个冒号，最后一个冒号之后的任何内容都不应该被视为关键字，它只需要在 length() 中添加 -1 即可。

Answer 2

select count(*) as projectcount, sum(hours) as totalhours from projects WHERE title like '%mykeyword:%';