对子查询使用"LIKE"运算符可返回多个结果 [英] Using 'LIKE' operator with a subquery that returns multiple results

问题描述

SQL新手.请帮助.

Newbie to SQL. Kindly help.

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

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字段中任何地方具有"world"的记录的数量.我的情况是，我有第二个查询，该查询返回了诸如"world"之类的模式列表.如何获取每个模式的计数?

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.

这篇关于对子查询使用"LIKE"运算符可返回多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！