mysql where搜索两个相同,mysql：查找具有多个标记和相同ID的行

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

Assume my tables look like this:

Links

md5 url title numberofsaves

-----------------------------------------

a0a0 google.com foo 200

b1b1 yahoo.com yahoo 100

Tags

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'

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.

Anyone know the solution?

解决方案

The type of problem is called 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

OUTPUT

╔══════╦════════════╦═══════╗

║ MD5 ║ URL ║ TITLE ║

╠══════╬════════════╬═══════╣

║ a0a0 ║ google.com ║ foo ║

╚══════╩════════════╩═══════╝