oracle 将“LIKE”运算符与返回多个结果的子查询一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18049602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using 'LIKE' operator with a subquery that returns multiple results
提问by user2268135
Newbie to SQL. Kindly help.
SQL 新手。请帮助。
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%';
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 ?
现在此代码将返回在 TableA.comment 字段中的任何位置具有“world”的记录数。我的情况是，我有一个第二个查询，它返回了一个模式列表，如“世界”。如何获得每个模式的计数？
My end result should just be 2 columns, first column pattern, second column count_of_pattern.
我的最终结果应该只是 2 列，第一列模式，第二列 count_of_pattern。
回答by Gordon Linoff
You can use liketo join the subquery to the table:
您可以使用like将子查询加入表：
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;
This assumes that the patterndoes not have wildcard characters. If it does, then you do not need to do the concatenation.
这假设pattern没有通配符。如果是，那么您不需要进行串联。
This also uses a left outer joinso that all patterns will be returned, even with no match.
这也使用 aleft outer join以便返回所有模式，即使没有匹配。
