我想查询一个表的名称列表,该表显示在另一个表的字段中.
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.
更多推荐
从一个表中查询出现在另一表的字段中的名称列表
发布评论