SQL通过4个表中的数据搜索人

Question

我正在尝试制作一个搜索引擎，它可以根据people表和其他三个表(phones、emails、addresses)中的信息来查找人。用户可以选择0到4个搜索参数(请参阅我的sql代码中的WHERE子句)，如果选择了0参数，所需的输出就是整个数据库。

我可以一次搜索一个表并记住ID，但这似乎效率低下。

我还有这样一个SQL，它可以在单个查询中完成所有操作，但它的工作方式与预期不同：

SELECT 
  people.id,
  name,
  group_concat(
    DISTINCT concat( number, ' (', phones.description, ')' )
    ORDER BY phones.description
    SEPARATOR '\n'
  ) AS phones,
  group_concat(
    DISTINCT concat( email, ' (', emails.description, ')' )
    ORDER BY emails.description
    SEPARATOR '\n'
  ) AS emails,
  group_concat(
    DISTINCT concat( address, ' (', addresses.description, ')' )
    ORDER BY addresses.description
    SEPARATOR '\n'
  ) AS addresses
FROM people
LEFT OUTER JOIN phones
  ON phones.person_id = people.id
LEFT OUTER JOIN emails
  ON emails.person_id = people.id
LEFT OUTER JOIN addresses
  ON addresses.person_id = people.id
WHERE
  name LIKE ?
  AND number LIKE ?
  AND email LIKE ?
  AND address LIKE ?
GROUP BY people.id
ORDER BY people.id DESC

此查询的问题如下：

1. 如果我按电话号码搜索，而某人有多个电话号码，则结果查询将只包括我搜索的号码，而不包括该人的其他号码。
2. 它效率极低，对数据库进行过多的迭代，并多次返回相同的实体(这就是为什么我最后在group_concat(...)函数中使用了group_concat(...))。

创建示例数据库的SQL命令：

CREATE TABLE people (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(120)
);

CREATE TABLE phones (
person_id INT UNSIGNED NOT NULL,
number VARCHAR(25) NOT NULL,
description VARCHAR(25)
);

CREATE TABLE emails (
person_id INT UNSIGNED NOT NULL,
email VARCHAR(100) NOT NULL,
description VARCHAR(25)
);

CREATE TABLE addresses (
person_id INT UNSIGNED NOT NULL,
address VARCHAR(200) NOT NULL,
description VARCHAR(25)
);

Answer 1

在@Barmar给我指明了正确的方向之后，我想出了下面的代码：

SELECT
  id,
  name,
  group_concat(
    DISTINCT concat( number, ' (', phones.description, ')' )
    ORDER BY phones.description
    SEPARATOR '\n'
  ) AS phones,
  group_concat(
    DISTINCT concat( email, ' (', emails.description, ')' )
    ORDER BY emails.description
    SEPARATOR '\n'
  ) AS emails,
  group_concat(
    DISTINCT concat( address, ' (', addresses.description, ')' )
    ORDER BY addresses.description
    SEPARATOR '\n'
  ) AS addresses

FROM people
LEFT OUTER JOIN phones
  ON phones.person_id = id
LEFT OUTER JOIN emails
  ON emails.person_id = id
LEFT OUTER JOIN addresses
  ON addresses.person_id = id

WHERE id IN (
  SELECT DISTINCT person_id
  FROM phones
  WHERE number LIKE ?
  AND person_id IN (
    SELECT DISTINCT person_id
    FROM emails
    WHERE email LIKE ?
    AND person_id IN (
      SELECT DISTINCT person_id
      FROM addresses
      WHERE address LIKE ?
    )
  )
)
AND name LIKE ?
GROUP BY id
ORDER BY id DESC;

Answer 2

若要避免在所有子表之间创建交叉积，请在子查询中执行这些搜索。

要从子表中获取与person相关的所有信息，而不仅仅是匹配的行，请执行第二个联接。

SELECT 
  people.id,
  name,
  group_concat(DISTINCT
    concat(number, ' (', p1.description, ')' )
    ORDER BY p1.description
    SEPARATOR '\n'
  ) AS phones,
  group_concat(DISTINCT
    concat(email, ' (', e1.description, ')' )
    ORDER BY e1.description
    SEPARATOR '\n'
  ) AS emails,
  group_concat(DISTINCT
    concat(address, ' (', a1.description, ')' )
    ORDER BY a1.description
    SEPARATOR '\n'
  ) AS addresses
FROM people
LEFT OUTER JOIN (
     SELECT DISTINCT person_id
     FROM phones
     WHERE number LIKE ?) AS phones
  ON phones.person_id = person.id
LEFT OUTER JOIN phones AS p1 ON p1.person_id = person.id
LEFT OUTER JOIN (
     SELECT DISTINCT person_id
     FROM emails
     WHERE email LIKE ?) AS emails
  ON emails.person_id = person.id
LEFT OUTER JOIN emails AS e1 ON e1.person_id = person.id
LEFT OUTER JOIN (
     SELECT DISTINCT person_id
     FROM addresses
     WHERE address LIKE ?) AS addresses
  ON addresses.person_id = person.id
LEFT OUTER JOIN addresses AS a1 ON a1.person_id = person.id
WHERE
  name LIKE ?
GROUP BY people.id
ORDER BY people.id DESC