SQL查找相似内容

Question

我有表 ITEMS 和列 URL。我只需要在 items.url 中找到相似的行:

两个相似行的示例:

ITEM_ID  |    URL    
1        | www.google.com/test1/test2/test3.php       
2        | www.yahoo.com/test1/test2/test3.php
3        | www.google.com/test5.php
4        | www.facebook.com/test5.php

如您所见，URL 相似，只是域不同。

我的查询应该是这样的:

SELECT * FROM ITEMS
WHERE URL LIKE `%google.com%`...
AND `here code probably` ???

我的查询应该返回 ITEM_ID 2 和 4

Answer 1

您可以按从“/”字符开始的子字符串进行分组，并取组中的最大 ID。使用 postgresql 语法，它应该是这样的:

SELECT *
  FROM ITEMS t
 WHERE t.item_id IN (SELECT MAX(s.item_d)
                       FROM ITEMS s
                      GROUP BY SUBSTRING(s.url FROM POSITION('/' IN s.url)))
 ORDER BY t.item_id;

更新:如果您只想要在不同域上具有相似行的 google 域，则可以使用过滤器 EXISTS:

SELECT *
  FROM ITEMS t
 WHERE t.url LIKE 'www.google.com%'
   AND EXISTS (SELECT 1
                 FROM ITEMS s
                WHERE s.url NOT LIKE 'www.google.com%'
                  AND SUBSTRING(t.url FROM POSITION('/' IN t.url)) =
                      SUBSTRING(s.url FROM POSITION('/' IN s.url)));