none
sqlite如何对多表进行差集查询? RRS feed

  • 问题

  • sqlite如何对多表进行差集查询?,目前只看到交集和并集的查询语句。差集查询是查询多表中非交集的部分
    2017年2月16日 12:05

答案

  • 你好,

    可以通过Not exist 和 left out join 来实现。例如,有两张表, 我们要查出tag表的 3 和 5

    sqlite> select * from lookup;
    node|id
    1|1
    1|2
    2|4
    2|6
    sqlite> select * from tag;
    tagid|data
    1|bar
    2|baz
    3|geek
    4|foo
    5|bank
    6|auto


    #Not Exist

    select tagid from tag t where  not exists (select  'x'  from  lookup l where l.id = t.tagid)

    #Left OUTER JOIN

    select
        t.tagid
    from
        tag t
            left outer join
        lookup l
            on t.tagid = l.id
    where
        l.id is null;

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2017年2月17日 9:00
    版主