如何在现有表上设计这些查询

Currently I have some existing tables about user action and user info and their associated meta info:

目前我有一些关于用户操作和用户信息及其相关元信息的现有表格：

action table:

行动表：

user_id, action_detail
1        "action_click"
2        "action_drag"

user info tablle

用户信息标签

user_id, full_name, email
1        "User One" "userone@user.com"
1        "User Two" "usertwo@user2.com"

company info table

公司信息表

company_name,      company_domain
"User Company"     "user.com"
"User2 Company"    "user2.com"

The new requirement I got is:

我得到的新要求是：

Building queries that can find all the actions of:

构建可以找到以下所有操作的查询：

all users from a single company
来自一家公司的所有用户
a single company but exclude certain users specified
单个公司，但不包括指定的某些用户
multiple companies together but exclude certain users specified
多个公司在一起但排除某些指定的用户

Could anyone give some thoughts about it( especially what is the efficient way to do 2 and 3)?

任何人都可以对此有所了解（特别是2和3的有效方法是什么）？

1 个解决方案

#1

Requirement #2 is a subset of requirement #3 (a single company is just a list of companies with the size of one). You could use the exists operator to find users under the companies domain, and exclude users based on other conditions:

要求＃2是要求＃3的子集（单个公司只是一个大小为1的公司列表）。您可以使用exists运算符查找公司域下的用户，并根据其他条件排除用户：

SELECT *
FROM   user u
WHERE  full_name NOT IN ('John Doe', 'Jane Doe' /* or any other condition */) AND
       EXISTS (SELECT *
               FROM   company c
               WHERE  c.company_name NOT IN ('company1', 'company2', /* etc. */) AND
                      u.email LIKE '%@' || c.company_domain)

EDIT:

编辑：

To address the conversation in the comments, if you have a large number of ignored users, you may want to have an auxiliary table of ignored users so you can index them and make the search faster.

要在注释中处理对话，如果您有大量被忽略的用户，您可能希望拥有一个被忽略用户的辅助表，以便您可以对它们编制索引并加快搜索速度。

E.g.:

例如。：

CREATE TABLE ignored_users (
  full_name VARCHAR PRIMARY KEY
);

INSERT INTO ignored_users VALUES ('John Doe');
-- A bunch of other inserts...
SELECT *
FROM   user u
WHERE  full_name NOT IN (SELECT full_name FROM ignored_users) AND
       EXISTS (SELECT *
               FROM   company c
               WHERE  c.company_name NOT IN ('company1', 'company2', /* etc. */) AND
                      u.email LIKE '%@' || c.company_domain)

#1