这是一个棘手的问题,我几乎想说“不要尝试这样做只是一个查询”。
我从编程的角度处理这样的 SQL 查询,因为我觉得结果往往不那么“神奇”。 (我在太多查询中看到了一个属性——现在的 SQL 查询似乎是用键盘上的猴子编写的……)
- Figure out which company IDs we want to list. This is the union of these two things:
- 任何与姓名或号码匹配的“人员”结果
- 任何与姓名或号码匹配的“公司”结果
- 列出该公司的电话号码以及人员。
我们先做#2:
SELECT
companyname AS name,
phone
FROM
customers
WHERE id IN (company_ids we want)
UNION
SELECT
name, phone
FROM
contacts
WHERE companyid IN (company_ids we want)
由于“company_ids we Want”将是一个查询,因此重新排列它以将其简化为仅出现 1 次:
SELECT
name, phone
FROM
(
SELECT
id AS companyid,
companyname AS name,
phone
FROM
customers
UNION
SELECT companyid, name, phone FROM contacts
) AS entities
WHERE
companyid IN (company_ids we want)
现在,为了填写有趣的部分,我们需要回答#1:
第 #1.1 部分是:
SELECT companyid FROM contacts WHERE name = $search OR number = $search;
第 #1.2 部分是:
SELECT id AS companyid FROM customers WHERE companyname = $search OR number = $search;
(注意$search
是我们的输入 - 每个 SQL 供应商的参数化查询差异很大,因此请根据需要替换该语法。)
Put the UNION
这两个中的IN
,我们就完成了:
SELECT
name, phone
FROM
(
SELECT
id AS companyid,
companyname AS name,
phone
FROM
customers
UNION
SELECT companyid, name, phone FROM contacts
) AS entities
WHERE
companyid IN (
SELECT companyid FROM contacts WHERE name = $search OR phone = $search
UNION
SELECT id AS companyid FROM customers WHERE companyname = $search OR phone = $search
)
;
并祈祷数据库能够找出一个查询计划,在合理的时间内执行此操作。您确定不想多次往返数据库吗?
请注意方法:我们确定了我们想要的内容(“与某些公司 ID 匹配的客户/联系人的姓名/电话”),然后找出缺失的部分(“哪个公司 ID?”)。这是因为,一旦您匹配了公司中的某个特定人员(例如,山姆),您就想要everyone来自该公司,加上该公司,或具有该公司 ID 的所有内容。知道了这一点,我们就得到了外部查询(#2),然后我们只需要弄清楚如何确定我们感兴趣的公司。
请注意,这些不会(并且 SQL 查询,没有 ORDER BY 也不会)以您相当奇特的顺序返回查询。但是,您可以向内部查询添加辅助列,并完成此操作:
SELECT
name, phone
FROM
(
SELECT
0 AS is_person,
id AS companyid,
companyname AS name,
phone
FROM
customers
UNION
SELECT 1 AS is_person, companyid, name, phone FROM contacts
) AS entities
WHERE
companyid IN (
SELECT companyid FROM contacts WHERE name = $search OR phone = $search
UNION
SELECT id AS companyid FROM customers WHERE companyname = $search OR phone = $search
)
ORDER BY
companyid, is_person, name
;
您还可以使用is_person
列(如果将其添加到SELECT
)如果您需要将结果分段以获取此查询的结果。
(如果您最终使用了这么长的查询,请看在上帝的份上,-- comment them!
)