我有三个PHP数组，它们都有相同的两个键：pId和name。阵列的名称为added、deleted&updated。
我想比较的表格如下：
id
relationId
pId
name
changeType
行由relationId分组，并且可能存在多个pId&每个relationId的名称对。changeType可以是added、deleted或updated。
我想要的是能够运行单个查询，该查询检查一组pId&与三个数组完全匹配的name对（其中changeType与数组名称匹配）存在于表中，并返回relationId。
这可能吗？
示例
表：
+--------+----------------+---------+---------------+----------------+
| **id** | **relationId** | **pId** | **name** | **changeType** |
+--------+----------------+---------+---------------+----------------+
| 1 | 1 | 1 | Smith | added |
+--------+----------------+---------+---------------+----------------+
| 2 | 1 | 2 | John | updated |
+--------+----------------+---------+---------------+----------------+
| 3 | 1 | 3 | Dexter | deleted |
+--------+----------------+---------+---------------+----------------+
| 4 | 1 | 4 | Heisenberg | added |
+--------+----------------+---------+---------------+----------------+
| 5 | 2 | 4 | Heisenberg | added |
+--------+----------------+---------+---------------+----------------+
| 6 | 2 | 3 | Dexter | updated |
+--------+----------------+---------+---------------+----------------+
| 7 | 2 | 3 | Dexter Morgan | updated |
+--------+----------------+---------+---------------+----------------+
PHP数组：
$added = array(
[1] = array(
pId => 4,
name => 'Heisenberg'
)
)
$deleted = array(
//Empty
)
$updated = array(
[1] = array(
pId => 3,
name => 'Dexter'
)
[2] = array(
pId => 3,
name => 'Dexter Morgan'
)
)
使用此数组进行查询时，返回的relationId应为2。
我通过将数组中的内爆字符串与表上的GROUP_CONCAT进行比较来解决这个问题。由于您通常不能在WHERE子句中使用GROUP_CONCAT，因此我不得不对FROM子句进行变通。
<?php
$s = '';
foreach($updated as $u){
$s .= $u['pId'] . $u['name'] . 'updated,';
}
foreach($deleted as $d){
$s .= $d['pId'] . $d['name'] . 'deleted,';
}
foreach($added as $a){
$s .= $a['pId'] . $a['name'] . 'added,';
}
//remove last comma
$s = rtrim($s, ',');
$stmt = $mysqli -> prepare("
SELECT
relationId
FROM (
SELECT
relationId,
GROUP_CONCAT(pId, name, changeType ORDER BY changeType DESC, id ASC) AS personConcat
FROM
persons
GROUP BY relationId
) x
WHERE personConcat = ?
");
$stmt -> bind_param('s', $s);
$stmt -> execute();
?>