将“LIKE"运算符与返回多个结果的子查询一起使用

2022-01-23 00:00:00 sql-like subquery oracle

SQL 新手.请帮忙.

Newbie to SQL. Kindly help.

对于多个模式，我需要计算在其中一个字段中具有模式的记录数.我知道如何为一个模式执行此操作，但是当子查询有多个模式时，如何计算每个模式的计数.我正在使用甲骨文.我会尝试用一个例子来解释.

I need to count number of records which have a pattern in one of the fields, for multiple patterns. I know how to do it for one pattern, but how do I get count of each pattern when there are multiple patterns coming from a subquery. I am using Oracle. I will try to explain with an example.

SELECT count(*) FROM TableA
WHERE 
TableA.comment LIKE '%world%';

现在，此代码将返回 TableA.comment 字段中任何位置具有世界"的记录数.我的情况是，我有一个第二个查询，它返回一个模式列表，如世界".我如何获得每个模式的计数?

Now this code will return the number of records which have 'world' anywhere in the TableA.comment field. My situation is, I have a 2nd query which has returns a list of patterns like 'world'.How do I get the count of each pattern ?

我的最终结果应该是 2 列，第一列模式，第二列 count_of_pattern.

My end result should just be 2 columns, first column pattern, second column count_of_pattern.

推荐答案

可以使用like将子查询加入表中:

You can use like to join the subquery to the table:

SELECT p.pattern, count(a.comment)
FROM (subquery here that returns "pattern"
     ) p left outer join
     TableA a
     on a.comment like '%'||p.pattern||'%'
group by p.pattern;

这假定 pattern 没有通配符.如果是这样，那么您不需要进行连接.

This assumes that the pattern does not have wildcard characters. If it does, then you do not need to do the concatenation.

这也使用了 left outer join 以便返回所有模式，即使没有匹配.

This also uses a left outer join so that all patterns will be returned, even with no match.

相关文章