我正在制作用户列表。我想返回与特定字段匹配的用户（和项目）（匹配电子邮件、匹配电子邮件和类型、匹配名称和类型或匹配所有字段）。我想减少重复用户，所以如果电子邮件和类型匹配，请不要返回电子邮件。
示例数据：
+----+---------------------+--------+---------+
| id | email | type | name |
+----+---------------------+--------+---------+
| 1 | rod@example.com | user | Rod |
| 2 | jane@example.com | user | Jane |
| 3 | rod2@example.com | user | Rod |
| 4 | rod3@example.com | admin | Rod |
| 5 | helen@example.com | admin | helen |
+----+---------------------+--------+---------+
因此，如果$email = 'rod@example.com'，$type = '用户'，$name = 'Rod' 我想返回以下结果。我想返回以下内容：
+----------------+----+
| matches | id |
+----------------+----+
| 3 (all) | 1 |
| 2 (name, type) | 3 |
| 1 (name) | 4 |
+----------------+----+
我正在考虑为每个匹配集提供单独的sql查询，1个用于电子邮件，另一个用于电子邮件和类型，另一个用于名称和类型，1用于所有匹配集。然后最后使用 php 过滤结果。
$users = array();
// match email
$sql = 'SELECT u.*, GROUP_CONCAT(i.name) as items
FROM users u
INNER JOIN items i
ON u.id = i.user_id
WHERE u.email = '.$email.'
GROUP BY u.id';
$users[] = mysqli_query($sql);
// match email & type
$sql = 'SELECT u.*, GROUP_CONCAT(i.name) as items
FROM users u
INNER JOIN items i
ON u.id = i.user_id
WHERE u.email = '.$email.' AND u.type = '.$type.'
GROUP BY u.id';
$users[] = mysqli_query($sql);
// match name & type
$sql = 'SELECT u.*, GROUP_CONCAT(i.name) as items
FROM users u
INNER JOIN items i
ON u.id = i.user_id
WHERE u.name = '.$name.' AND u.type = '.$type.'
GROUP BY u.id';
$users[] = mysqli_query($sql);
// match email, name & type
$sql = 'SELECT u.*, GROUP_CONCAT(i.name) as items
FROM users u
INNER JOIN items i
ON u.id = i.user_id
WHERE u.email = '.$email.' AND u.name = '.$name.' AND u.type = '.$type.'
GROUP BY u.id';
$users[] = mysqli_query($sql);
我觉得必须有一种方法可以创建一个查询来替换它们并避免使用 php 进行任何过滤。谁能提供任何建议？
我会这样做：
SELECT id,
email = 'rod@example.com' EmailMatch,
type = 'user' TypeMatch,
name = 'Rod' NameMatch
FROM t
HAVING EmailMatch + TypeMatch + NameMatch > 0
ORDER BY EmailMatch + TypeMatch + NameMatch DESC
输出：
| ID | EMAILMATCH | TYPEMATCH | NAMEMATCH |
|----|------------|-----------|-----------|
| 1 | 1 | 1 | 1 |
| 3 | 0 | 1 | 1 |
| 2 | 0 | 1 | 0 |
| 4 | 0 | 0 | 1 |
在这里摆弄。
您还可以更改表中实际字段的id，并避免以后的连接，因为您将直接从同一查询中检索数据。
如果您确实需要实际查看每行的总匹配数，则可以添加另一行，在其中复制所有条件并添加它们或将查询包装在另一个 select 语句中。无论哪种方式都不容易阅读或效率低下。所以我建议，如果你确实需要查看每行的匹配计数，那么在 PHP 中添加匹配列。
我会用一个 CASE WHEN 来执行（抱歉，如果语法不是 100% 正确的）：
$sql = 'SELECT u.id,
CASE WHEN (u.name = ' .$name. ' AND u.type = '.$type.' AND u.email = '.$email.') THEN "3 (all)"
ELSE WHEN (u.name = ' .$name. ' AND u.type = '.$type.') THEN "2 (name, type)"
ELSE WHEN (u.name = ' .$name. ' AND u.emal = '.$email.') THEN "2 (name, email)"
ELSE WHEN (u.type = ' .$type. ' AND u.email = '.$email.') THEN "2 (email,type)"
ELSE WHEN (u.name = ' .$name. ') THEN "1 (name)"
ELSE WHEN (u.type = ' .$type. ') THEN "1 (type)"
ELSE WHEN (u.email = ' .$email. ') THEN "1 (email)"
ELSE "0" END AS matches
FROM users u
HAVING matches != "0"';
这可能是可能的查询：
select (CASE
WHEN CONCAT(users.name, '@')=LEFT(users.email, LENGTH(users.name)+1)
and users.type='user' THEN "3 (all)"
WHEN users.type='user' and CONCAT(users.name, '@')!=LEFT(users.email,
LENGTH(users.name)+1) THEN "2 (name, type)"
ELSE "1 (name)" END)matches, users.id from users
inner join items on users.id=items.user_id and
users.name in (select users.name from users
group by users.name having count(users.name)>1);
输出：
+----------------+----+
| matches | id |
+----------------+----+
| 3 (all) | 1 |
| 2 (name, type) | 3 |
| 1 (name) | 4 |
+----------------+----+