mysql交叉检查标签查询

发布于 2024-10-18 14:16:13 字数 555 浏览 3 评论 0原文

我有两个表，一个包含项目，一个包含标签，我想找到项目表中具有标签“foo”但不包含标签栏的每个项目，所以如果我要选择具有标签“foo”的所有项目，我的查询将是

 SELECT * FROM items i INNER JOIN tags t on i.id= t.id WHERE tag= "foo"

，我得到所有带有“foo”标签的项目，那么我如何子查询以取出所有也标记为“bar”的项目

编辑基于下面提供的 Matthew Coxa 解决方案，我想出了这个，如果有人发现任何缺陷，请告诉我

      SELECT * FROM items i INNER JOIN 
        tags t ON t.item_id=i.id 
        WHERE tag = 'foo' 
        AND NOT EXISTS (
            SELECT * FROM tags tt WHERE t.item_id = tt.item_id AND tag = 'bar'
        )

原文

I have two tables one with Items and one with tags I want to find every item in the items table that has the tag "foo" but not the tag bar so if I where going to select all items that have the tag "foo" my query would be

 SELECT * FROM items i INNER JOIN tags t on i.id= t.id WHERE tag= "foo"

and I get all items with tags of "foo" so how do I subquery to take out all the items that are also tagged "bar"

EDIT
based on Matthew Coxa solution provided bellow I cam up with this if anyone sees any flaw with this please let me know

      SELECT * FROM items i INNER JOIN 
        tags t ON t.item_id=i.id 
        WHERE tag = 'foo' 
        AND NOT EXISTS (
            SELECT * FROM tags tt WHERE t.item_id = tt.item_id AND tag = 'bar'
        )

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

零崎曲识 2024-10-25 14:16:13

SELECT *
FROM items i

INNER JOIN tags t
ON i.id = t.id

WHERE t.tag = "foo" AND NOT EXISTS (
    SELECT *
    FROM items ii

    INNER JOIN tags tt
    ON ii.id = tt.id 

    WHERE tt.tag = "bar" AND tt.id = t.id)

SELECT *
FROM items i

INNER JOIN tags t
ON i.id = t.id

WHERE t.tag = "foo" AND NOT EXISTS (
    SELECT *
    FROM items ii

    INNER JOIN tags tt
    ON ii.id = tt.id 

    WHERE tt.tag = "bar" AND tt.id = t.id)

回复收藏 0 原文

老娘不死你永远是小三 2024-10-25 14:16:13

select
    *
from
    items i
where
    exists (select * from tags t where t.id = i.id and t.tag = "foo")
and
    not exists (select * from tags t where t.id = i.id and t.tag = "bar")

或者

;with ItemTagCount as (
    select
        t.id,
        sum(case t.tag when "foo" then 1 else 0 end) as FooCount,
        sum(case t.tag when "bar" then 1 else 0 end) as BarCount
    from
        tags t
    group by
        t.id
)
select
    i.*
from
    items i
inner join
    ItemTagCount itc on itc.id = i.id
where
    itc.FooCount >= 1 and itc.BarCount = 0

select
    *
from
    items i
where
    exists (select * from tags t where t.id = i.id and t.tag = "foo")
and
    not exists (select * from tags t where t.id = i.id and t.tag = "bar")

;with ItemTagCount as (
    select
        t.id,
        sum(case t.tag when "foo" then 1 else 0 end) as FooCount,
        sum(case t.tag when "bar" then 1 else 0 end) as BarCount
    from
        tags t
    group by
        t.id
)
select
    i.*
from
    items i
inner join
    ItemTagCount itc on itc.id = i.id
where
    itc.FooCount >= 1 and itc.BarCount = 0

回复收藏 0 原文

~没有更多了~