提问人：Dario Ferrer 提问时间：11/1/2023 更新时间：11/1/2023 访问量：65
MySQL：如何通过SQL查询筛选列结果
MySQL: How to filter results on columns through a SQL query
问：
我需要知道以下问题是否可以用 SQL 查询解决以及如何解决。我用PHP以不同的方式完成了它，但是内存猛增，服务器需要很长时间才能返回结果。:(
- 我有一个名为的表，其中包含许多行（但很多行）。
'parts' - 每一行都有列 和 ，它们都具有不同的值。
part1part2part3
例：
+---------+-------+-------+
| part1 | part2 | part3 | <-- Column Name
+---------+-------+-------+
| chamber | wheel | door | <-- Value
+---------+-------+-------+
另一方面，我有一个来自外部源的三个值的列表：
$list = ['mirror', 'seat', 'door'];
我需要选择其列包含所有这些值的行，而不考虑顺序。有效行的示例如下：
+-------+--------+-------+
| part1 | part2 | part3 |
+-------+--------+-------+
| door | mirror | seat |
+-------+--------+-------+
有时外部列表没有 3 个值，只有 1 或 2 个值：
$list = ['antenna'];
...然后，有效行将是包含该值的行，例如：
+--------+--------+---------+
| part1 | part2 | part3 |
+--------+--------+---------+
| engine | mirror | antenna |
+--------+--------+---------+
- 附加信息：在每一行中，列没有重复值，因此在查询中可以忽略此功能。
- 我需要知道如何对 SELECT 进行查询，对 COUNT 进行另一个查询。
- 必须通过MySQL查询解析结果，而不使用PHP进行过滤。
答：
0赞
Akina
11/1/2023
#1
可能的解决方案。
CREATE TABLE parts
SELECT 'chamber' part1, 'wheel' part2, 'door' part3 UNION ALL
SELECT 'door', 'mirror', 'seat' UNION ALL
SELECT 'engine', 'mirror', 'antenna';
SELECT * FROM parts;
| 第 1 部分 | 第二部分 | 第 3 部分 |
|---|---|---|
| 室 | 轮子 | 门 |
| 门 | 镜子 | 座位 |
| 发动机 | 镜子 | 天线 |
SELECT parts.*
FROM parts
CROSS JOIN (
SELECT 'mirror' UNION
SELECT 'seat' UNION
SELECT 'door'
) criteria (value)
GROUP BY parts.part1, parts.part2, parts.part3
HAVING SUM(criteria.value IN (parts.part1, parts.part2, parts.part3)) = COUNT(*)
| 第 1 部分 | 第二部分 | 第 3 部分 |
|---|---|---|
| 门 | 镜子 | 座位 |
SELECT parts.*
FROM parts
CROSS JOIN (
SELECT 'mirror' UNION
SELECT 'antenna'
) criteria (value)
GROUP BY parts.part1, parts.part2, parts.part3
HAVING SUM(criteria.value IN (parts.part1, parts.part2, parts.part3)) = COUNT(*)
| 第 1 部分 | 第二部分 | 第 3 部分 |
|---|---|---|
| 发动机 | 镜子 | 天线 |
SELECT parts.*
FROM parts
CROSS JOIN (
SELECT 'antenna'
) criteria (value)
GROUP BY parts.part1, parts.part2, parts.part3
HAVING SUM(criteria.value IN (parts.part1, parts.part2, parts.part3)) = COUNT(*)
| 第 1 部分 | 第二部分 | 第 3 部分 |
|---|---|---|
| 发动机 | 镜子 | 天线 |
如果你不想将你的条件数组转换为子查询文本（必须连接成），那么你可以将其转换为JSON数组形式，将其提供给MySQL，并使用JSON_TABLE（）解析静态子查询中的分隔值。
评论
0赞
Dario Ferrer
11/1/2023
明菜谢谢你的回答。现在将审查和测试您的代码，并通知您。:)
0赞
Rob Eyre
11/1/2023
#2
另一种方法，以@CBroe的建议为基础。如果您可以在将条件插入 SQL 语句之前对条件进行排序，那么这可能适合您。
首先规范化零件列表，然后按排序顺序重新组合一串匹配零件，用逗号分隔：
SELECT id, GROUP_CONCAT(part ORDER BY part) AS p
FROM (
SELECT id, part1 AS part FROM parts
UNION ALL SELECT id, part2 FROM parts
UNION ALL SELECT id, part3 FROM parts
) partslist
WHERE part IN (?, ?, ?)
GROUP BY id
最后，在提取匹配行的整体查询中包装：
SELECT parts.*
FROM (
SELECT id, GROUP_CONCAT(part ORDER BY part) AS p
FROM (
SELECT id, part1 AS part FROM parts
UNION ALL SELECT id, part2 FROM parts
UNION ALL SELECT id, part3 FROM parts
) partslist
WHERE part IN (?, ?, ?)
GROUP BY id
) partsjoined
INNER JOIN parts ON partsjoined.id = parts.id
WHERE partsjoined.p = CONCAT_WS(',', ?, ?, ?)
（根据需要减少 ？ 参数的数量）
同样，这假定您能够在将条件参数绑定到语句之前对其进行排序。
评论
0赞
Akina
11/1/2023
CONCAT_WS(',', ?, ?, ?)必须根据 中应用的排序规则进行排序。如果值包含国家符号，则可能会有问题。GROUP_CONCAT()
0赞
Rob Eyre
11/1/2023
公平点。仔细想想，按字符串方式执行此操作可能性能也不是很好。@Akina的解决方案更简洁
评论