dongyi1441 2015-12-30 00:36 采纳率: 100%
浏览 384
已采纳

使用多个标签搜索标签表或在MySQL中选择“全部除外”

Is there a way to select all except those who match?

I am working with a tag table (article_id, tag) and an article table (article_id, title, ...);

My current problem: The existing search worked like this:

SELECT DISTINCT `article_id` FROM `TagTable` WHERE `tag` IN ('tag1', 'tag2')

Unfortunately this way the user gets the results that contain either one of the tags while you would only want articles that match both tags.

How would you implement it? Several subqueries for each tag seems a dumb idea. My idea would be to select all and remove everyone that dose not have each of the tags. Any better Ideas?

Thank you in advance.

  • 写回答

1条回答 默认 最新

  • dongliao6777 2015-12-30 00:46
    关注

    hmm.

    Well the easiest way is to use a subquery:

    SELECT a.*
    FROM articles a
    WHERE a.id IN (
        SELECT article_id FROM TagTable WHERE tag = 'tag1'
    ) AND a.id IN (
        SELECT article_id FROM TagTable WHERE tag = 'tag2'
    );
    

    But since you said you don't want to do that, you could try something like:

    SELECT article_id, count(DISTINCT tag)
    FROM TagTable
    WHERE tag IN ('tag1', 'tag2')
    GROUP BY article_id
    HAVING count(DISTINCT tag) = 2
    

    I haven't tested it yet, but something like that should work. you just have to make sure the HAVING clause equals the number of tags you are looking for.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 三电平逆变器中点电位平衡问题
  • ¥20 这怎么写啊 java课设
  • ¥15 双向孟德尔随机化反向是为什么显示参数长度为零
  • ¥15 用C语言完成一个复杂的游戏
  • ¥15 如何批量更改很多个文件夹里的文件名中包含文件夹名?
  • ¥50 MTK手机模拟HID鼠标出现卡顿
  • ¥20 求下下面这个数据结构代码
  • ¥20 前端 二进制文件流图片转化异常
  • ¥15 github上的这个C语言项目如何跑起来
  • ¥15 java 判断某个数 区间是否存在