从一个表中查询出现在另一表的字段中的名称列表 [英] Query a list of names from one table that appear in a field in a different table

问题描述

我想查询一个表的名称列表，该表显示在另一个表的字段中.

I want to query a list of names from one table that appear in a field in a different table.

示例:

table1.title>老虎伍兹作弊，老虎伍兹崩溃，布拉德·皮特很棒，麦当娜领养，布拉德·皮特拍电影

table1.title>Tiger Woods Cheats, Tiger Woods Crashes, Brad Pitt is Great, Madonna Adopts, Brad Pitt Makes a Movie

table2.names>泰格·伍兹，布拉德·皮特，麦当娜

table2.names>Tiger Woods, Brad Pitt, Madonna

这就是两个表和值.我想写一个查询来计算table2.names中哪个名称最常出现在table1.title中的查询

So those are the two tables and values. I would like to write a query that counts which names from table2.names appear most often in table1.title

有人建议使用内部联接，但是我无法使它正常工作……我感谢您的帮助！！谢谢.

Someone suggested using inner join, but I could not get it to work... I appreciate the help!! Thanks.

推荐答案

使用:

  SELECT a.names,
         COUNT(b.titles) AS num
    FROM TABLE_2 a
    JOIN TABLE_1 b ON INSTR(b.title, a.names) > 0
GROUP BY a.names
ORDER BY num DESC

请参阅有关 INSTR()的文档

See the documentation about INSTR() - checking for a value greater than 0 means the name occurred in the title, otherwise it would be zero.

AS num是列别名，您可以在ORDER BY中引用该别名，以升序或降序排序.

The AS num is a column alias, which you can reference in the ORDER BY to sort either in ASCending or DESCending order.

这篇关于从一个表中查询出现在另一表的字段中的名称列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！