MYSQL计数匹配值
提问于 2020-07-10 23:32:47
我有一个包含2列的数据库。character_id和killmail_id对象是获取特定character_id的所有killmail_ids的列表，然后获取所有具有匹配killmail_ids的character_ids，并计算这些character_ids出现的次数
到目前为止，我得到了这样的信息：($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_ids总数，我只希望它计数killmail_id，如果$target也有匹配的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;
}
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-07-11 01:14:56
一个选项使用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表示您的输入参数，您应该将其作为查询参数传递，而不是将其连接到查询字符串中。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62846416
复制
腾讯云开发者
