创建具有许多 SUBSTR 的选定 COUNT

Question

我正在尝试在SQL中创建一个代码，该代码必须返回某些子字符串的occourrencies数量。

我需要计算子重复的次数。

Ex:
Substring 'LQX' -> 10 times
Substring 'TES' -> 5 times
(select count(cod_ordem_producao) 
 from qt_qts.pla_ordem_producao
 where substr(cod_ordem_producao,1,3)='LQR') LQR,
(select count(cod_ordem_producao)
 from qt_qts.pla_ordem_producao
 where substr(cod_ordem_producao,1,3)='TES') TES
from Dual

如何在此代码中放置日期过滤器？（日期之间）

感谢

Answer 1

使用条件聚合：

select sum(case when cod_ordem_producao like 'LQR%' then 1 else 0 end) as lqr,
       sum(case when cod_ordem_producao like 'TES%' then 1 else 0 end) as tes
from qt_qts.pla_ordem_producao
where <whatever additional conditions you want>;

如果您更普遍地想知道字符串的前三个字符是什么，您可以使用group by：

select substr(cod_ordem_producao, 1, 3) as first_three,
       count(*)
from qt_qts.pla_ordem_producao
where <whatever additional conditions you want>
group by substr(cod_ordem_producao, 1, 3);

这会将值放在单独的行中，而不是放在单独的列中。

Answer 2

根据子字符串分组，然后筛选出日期

select substr(cod_ordem_producao,1,3) as substr_val
       ,count(cod_ordem_producao) 
  from qt_qts.pla_ordem_producao
 where substr(cod_ordem_producao,1,3) in ('LQR','TEST')
   and <date filter criteria>
group by substr(cod_ordem_producao,1,3)