MySQL有很多匹配的地方？

Well this one is a headscratcher for me. I've got a system of posts with tags. Tags are a many->many relationship with the posts.

The issue is that I'd like to select other posts based on how many tags they have matching with the current one.

A quick visual example:

PostA: TagA, TagB
PostB: TagC
PostC: TagA
PostD: TagA, TagB

So if I inputted PostA, it'd give me PostD, PostC.

I really don't even know where to start with this one, and I'm hoping somebody smarter than I ran into this issue already and can offer some help.

OK, so supposing your tagged posts table actually looks like this:

PostA   TagA
PostA   TagB
PostB   TagC
PostC   TagA
PostD   TagA
PostD   TagB

then you want

SELECT post FROM tagged_posts
  WHERE post != 'PostA'
  AND tag in (SELECT tag FROM tagged_posts WHERE post = 'PostA')
  GROUP BY post
  ORDER BY COUNT(*) desc

Here is the wordpress version if anyone is interested. It uses tags (but can be modified for categories easily). Hope it helps somebody else too.

Note: Two variables that are needed, the $wpdb class (which needs to be globalized if this is being called in a function), and $post->ID which is set automatically with a call to the_post(), but otherwise needs to be set by hand.

$query = $wpdb->get_results("
SELECT posts.*
FROM {$wpdb->term_relationships} as relate
LEFT JOIN {$wpdb->term_taxonomy} as taxonomy
    ON relate.term_taxonomy_id = taxonomy.term_taxonomy_id
LEFT JOIN {$wpdb->terms} as terms
    ON taxonomy.term_id = terms.term_id
LEFT JOIN {$wpdb->posts} as posts
    ON posts.ID = object_id
WHERE object_id != '{$post->ID}'
    AND taxonomy = 'post_tag'
    AND taxonomy.term_id in
        (
            SELECT term_id
            FROM {$wpdb->term_relationships} as relate
            LEFT JOIN {$wpdb->term_taxonomy} as taxonomy
                ON relate.term_taxonomy_id = taxonomy.term_taxonomy_id
            WHERE object_id = '{$post->ID}'
        )
GROUP BY object_id
ORDER BY COUNT(*) desc
");