MYSQL COUNT匹配值的数目

Question

我有一个包含2列的数据库。character_id和killmail_id对象是获取特定character_id的所有killmail_id的列表然后获取具有匹配killmail_id的所有character_id，并计算这些character_id出现的次数

到目前为止，我得到的是：($target是要搜索的character_id(

SELECT `character_id`, COUNT(`killmail_id`) FROM `attackers` WHERE $target NOT IN (SELECT `killmail_id` FROM `attackers` WHERE `character_id` <> $target) AND `character_id` <> $target  GROUP BY `killmail_id` ;

这几乎有效，但它计算了一个角色的killmail_id总数如果$target也有匹配的killmail_id ，我只希望它计算killmail_id

如果这没有意义，这就是我以前使用的(PHP(，如果可能的话，我会通过使用更具体的SQL语句来消除循环。

function seenWith($target){
$sql = "SELECT `killmail_id` FROM `attackers` WHERE character_id='" . $target . "';";
$mySql = $db->prepare($sql);
$mySql->execute();
$results = $mySql->fetchAll();
$friends = array();
foreach ($results as $idx => $array){
foreach ($array as $key => $value){
$sql = "SELECT `character_id`,`killmail_id` FROM `attackers` WHERE killmail_id='$value' AND character_id<> '".$target."';";
$mySql = $db->prepare($sql);
$mySql->execute();
$rData = $mySql->fetchAll();
foreach($rData as $index => $friendName){
array_push($friends, htmlspecialchars($friendName['character_id'], ENT_QUOTES));
}
}
}
return $friends;
}

Answer 1

一个选项使用exists:

select character_id, killmail_id
from attackers a
where
character_id <> :target
and exists (
select 1
from attackers a1
where 
a1.character_id = :target
and a1.killmail_id = a.killmail_id
)

:target表示您的输入参数，您应该将其作为查询参数传递，而不是将其连接到查询字符串中。