代码之家 › 专栏 › 技术社区 › Carson Myers

在SQL条件下使用表,而不将该表的内容合并到结果中

sqlite3 join sqlite

1

Carson Myers · 技术社区 · 14 年前

假设我有四张桌子, users , contacts , files 和 userfiles .

用户可以上载文件并拥有联系人。他们可以选择与联系人共享上传的文件。

当用户选择一个或多个已上载的文件时,我希望显示一个联系人列表,他们尚未与之共享所有选定文件。因此,如果他们选择了一个文件,它将显示那些还看不到该文件的联系人。如果选定的多个文件,它将显示无法看到的联系人 全部的 这些文件。

现在我正在尝试这样的查询(使用sqlite3):

select users.user_id, users.display_name
from users, contacts, userfiles
where contacts.user_id = :user_id
and contacts.contact_id = users.user_id
and (
    userfiles.user_id != users.user_id
    and userfiles.file_id != :file_id
);

请注意,最后一行是在多个选定文件的情况下在循环中自动生成的。

在哪里? :user_id 用户是否尝试共享该文件,以及 :file_id 是一个文件,如果用户已经可以看到该文件,那么结果中将省略这些文件。我最后得到的是一个正在共享的联系人列表任何所选联系人以外的其他文件,因此如果用户与任何一个联系人共享多个文件,则该联系人会多次显示在列表中。

我怎样才能避免重复?我只想检查文件是否已经被共享,而不是获取 用户文件 不涉及特定文件的。

2 回复 | 直到 14 年前

1

MPelletier 14 年前

选择users.user_id,users.display_name 来自用户,联系人为C 其中c.user_id=:user_id 和c.contact_id=users.user_id 不存在( 选择用户标识从用户文件作为UF 其中uf.user_id=c.contact_id 和uf.file_id in(:file_id) ;

注意 :file_ids 是所有文件的ID,用逗号分隔。不再循环运行多个查询!

编辑:

这是我作为测试运行的数据:

create table users (user_id integer primary key, display_name text);
insert into users values (1,"bob");
insert into users values (2,"jim");
insert into users values (3,"bill");
insert into users values (4,"martin");
insert into users values (5,"carson");

create table contacts values (user_id integer, contact_id integer);
insert into contacts select u1.user_id, u2.user_id from users u1, users u2 where ui.user_id!=u2.user_id;

create table userfiles (user_id integer, file_id integer);
insert into userfiles values (1,10);
insert into userfiles values (2,10);
insert into userfiles values (3,10);
insert into userfiles values (4,10);
insert into userfiles values (1,20);
insert into userfiles values (2,30);

然后,如果我用 :user_id = 5和 :files_id = 20,30,我得到:

select users.user_id, users.display_name
from users, contacts as c
where c.user_id = 5
and c.contact_id = users.user_id
and not exists (
    select user_id
    from userfiles as uf
    where uf.user_id = c.contact_id
    and uf.file_id in (20,30)
);

UserID|Display_Name
3     |bill
4     |martin

这似乎是你想要的,据我所知,那就是唯一没有所有文件ID的用户。如果我误解了什么,请告诉我。

2

1

Carson Myers 14 年前

这似乎是可行的,不确定它是否是最佳的,但这是我唯一能想到的方法:

select users.user_id, users.display_name
from users, contacts
where contacts.user_id = :user_id
and contacts.contact_id = users.user_id
and (
    select count(*)
    from userfiles
    where userfiles.user_id = users.user_id
    and userfiles.file_id in (:file_ids)
) < :number_of_files;

它选择所有联系人,但与所有联系人匹配的联系人除外。 file_ids . 它确实选择了与 文件文件 ,因为它获取与指定ID匹配的联系人的数量,然后检查是否少于所提供的ID数量。