mysql:查找具有多个标签和相同id的行 [英] mysql: finding rows that have multiple tags and the same id

问题描述

在进行两个表的JOIN操作时，我无法确定mysql查找具有两个特定标签"和相同"hashid"的链接的问题

I'm having an issue figuring the mysql to find links that have two specific 'tags' and the same 'hashid' when doing a JOIN of two tables

假设我的表格如下:

链接

md5     url         title   numberofsaves
-----------------------------------------
a0a0    google.com  foo     200
b1b1    yahoo.com   yahoo   100

标签

 md5    tag
 ---------------
 a0a0   awesome
 a0a0   useful
 a0a0   cool
 b1b1   useful
 b1b1   boring

我想返回同时具有有用"和很棒"标签的行

I want to return rows that have tags of BOTH 'useful' and 'awesome'

当前(工作/快速)查询，用于按1个标签查找链接:

The current (working/ fast) query for finding links by 1 tag:

SELECT links.title, links.numsaves FROM links LEFT JOIN tags ON links.md5=tags.md5 WHERE tags.tag = 'useful' ORDER BY links.numberofsaves DESC LIMIT 20

在阅读文章后我尝试使用以下内容:

After reading an article I tried to use the following:

SELECT links.title, links.numsaves FROM links LEFT JOIN tags ON links.md5=tags.md5 GROUP BY tags.md5 HAVING SUM(tags.tag='useful') AND SUM(tags.tag='awesome') ORDER BY links.numberofsaves DESC LIMIT 20

此确实有效，但是它是如此之慢以至于无法使用.

This does work but it is so unbelievably slow as to be unusable.

有人知道解决方案吗?

推荐答案

问题的类型称为Relational Division

SELECT  a.md5, 
        a.url,
        a.title
FROM    Links a
        INNER JOIN Tags b
            ON a.md5 = b.md5
WHERE   b.Tag IN ('awesome', 'useful') -- <<== list of desired tags
GROUP   BY a.md5, a.url, a.title
HAVING  COUNT(*) = 2                   -- <<== number of tags defined

• SQLFiddle演示
• 关系部门的SQL
• SQLFiddle Demo
• SQL of Relational Division

输出

╔══════╦════════════╦═══════╗
║ MD5  ║    URL     ║ TITLE ║
╠══════╬════════════╬═══════╣
║ a0a0 ║ google.com ║ foo   ║
╚══════╩════════════╩═══════╝

这篇关于mysql:查找具有多个标签和相同id的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！