MySQL/MariaDB中WHERE IN数组无法按项统计数量求助

问题场景 #

从接口传入数组，需统计数组内每个barcode在shelves表中的出现次数，但现有两种查询方式均无法满足需求：

• 结合barcode与count()子查询时，返回全表总数量，而非单个barcode的独立计数
• 仅用count(*)查询时，返回所有匹配barcode的总计数，无法按单个barcode分别统计

错误示例及结果 #

错误写法1：子查询未关联导致返回全表总数 #

$sql = "SELECT barcode, (SELECT count(barcode) bcnt FROM shelves) as bcount FROM shelves WHERE barcode IN ('".implode("','",$data['barcode'])."')";
$statement = $this->connection->prepare($sql);
$statement->execute();                      
$row = $statement->fetchAll();          
$this->response['data']=$row;
$this->response['error_code']=0;
$this->response['status']='success';
$this->response['message']='data retrieve for shelves';

返回结果：

{
    "error_code": 0,
    "status": "success",
    "message": "data retrieve for shelves",
    "error_message": "",
    "data": [
        {
            "barcode": "5902280031062",
            "bcount": "1485"
        },
        {
            "barcode": "5902280031062",
            "bcount": "1485"
        },
        {
            "barcode": "133",
            "bcount": "1485"
        }
    ]
}

注：所有bcount均为全表总条数，且重复返回同一barcode的多条记录

错误写法2：仅返回所有匹配barcode的总计数 #

$sql="SELECT count(*) FROM shelves WHERE barcode IN ('".implode("','",$data['barcode'])."')";

返回结果：

{
    "error_code": 0,
    "status": "success",
    "message": "data retrieve for shelves",
    "error_message": "",
    "data": [
        {
            "count(*)": "15"
        }
    ]
}

正确解决方案 #

方法1：GROUP BY分组统计（基础版） #

通过GROUP BY barcode按条码分组，配合COUNT(*)计算每组的出现次数，同时用IN限定统计范围，且使用预处理语句避免SQL注入：

// 生成占位符，适配预处理语句
$placeholders = implode(',', array_fill(0, count($data['barcode']), '?'));
$sql = "SELECT barcode, COUNT(*) as bcount FROM shelves WHERE barcode IN ($placeholders) GROUP BY barcode";

$statement = $this->connection->prepare($sql);
// 绑定传入的barcode数组作为参数
$statement->execute($data['barcode']);                      
$row = $statement->fetchAll();          

$this->response['data']=$row;
$this->response['error_code']=0;
$this->response['status']='success';
$this->response['message']='data retrieve for shelves';

返回结果示例：

{
    "error_code": 0,
    "status": "success",
    "message": "data retrieve for shelves",
    "error_message": "",
    "data": [
        {
            "barcode": "5902280031062",
            "bcount": "7"
        },
        {
            "barcode": "133",
            "bcount": "1"
        }
    ]
}

方法2：返回所有传入条码（含无匹配项） #

如果需要返回传入数组中所有条码（包括表中不存在的，计数为0），可通过LEFT JOIN关联临时表实现：

// 构造临时表SQL（适配MySQL 8.0+的VALUES语法）
$barcodeValues = array_map(function($bc) { return "('$bc')"; }, $data['barcode']);
$tempTable = implode(',', $barcodeValues);

$sql = "
    SELECT t.barcode, COUNT(s.barcode) as bcount 
    FROM (SELECT * FROM (VALUES $tempTable) AS temp(barcode)) t
    LEFT JOIN shelves s ON t.barcode = s.barcode
    GROUP BY t.barcode
";

// 若数据库不支持VALUES语法（如MySQL 5.x），替换为UNION ALL写法：
// $tempTable = implode(' UNION ALL ', array_map(function($bc) { return "SELECT '$bc' as barcode"; }, $data['barcode']));
// $sql = "SELECT t.barcode, COUNT(s.barcode) as bcount FROM ($tempTable) t LEFT JOIN shelves s ON t.barcode = s.barcode GROUP BY t.barcode";

$statement = $this->connection->prepare($sql);
$statement->execute();                      
$row = $statement->fetchAll();          

此写法会返回所有传入条码，表中无匹配的条码对应的bcount为0。

内容的提问来源于stack exchange，提问作者chized