哔哔one
我可能想得太离谱了,但这只是我想到的一种技术,它将确保所有位置 id 都会在结果集中收到平均值。假设$locations_loop(包含数组类型数据的变量的一个糟糕的名称,说实话)具有以下数据:$locations_loop = [ ['id' => 1], ['id' => 2], ['id' => 3], ['id' => 4],];并且您有一个具有以下架构的数据库表:(db-fiddle demo)CREATE TABLE `locations_rating` ( `id` int(11) NOT NULL, `l_id` int(11) NOT NULL, `stars` int(11) NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=latin1;INSERT INTO `locations_rating` (`id`, `l_id`, `stars`) VALUES(1, 3, 4),(2, 2, 2),(3, 1, 0),(4, 2, 5),(5, 3, 2),(6, 1, 10);id然后,您可以通过从值列创建一个“派生表” ,然后将数据库数据连接到其中,从而一次访问数据库即可获取所有数据。像这样的东西:SELECT def.l_id, ROUND(AVG(COALESCE(stars, 0)), 1) avgFROM ( (SELECT 1 AS l_id) UNION (SELECT 2) UNION (SELECT 3) UNION (SELECT 4) ) AS defLEFT JOIN locations_rating AS loc ON def.l_id = loc.l_idGROUP BY def.l_id要使用准备好的语句和绑定参数来执行此操作:$locationIds = array_column($locations_loop, 'id');$countIds = count($locationIds);$fabricatedRows = implode(' UNION ', array_fill(0, $countIds, '(SELECT ? AS l_id)'));$sql = "SELECT derived.l_id, ROUND(AVG(COALESCE(stars, 0)), 1) avg ($fabricatedRows) AS derived LEFT JOIN locations_rating as loc ON derived.l_id = loc.l_id GROUP BY def.l_id";$stmt = $pdo->prepare($sql);$stmt->execute($locationIds);var_export($stmt->fetchAll(PDO::FETCH_ASSOC));应该输出:(我测试了该技术在我的本地环境中是否成功)[ ['l_id' => 1, 'avg' => 5.0], ['l_id' => 2, 'avg' => 3.5], ['l_id' => 3, 'avg' => 3.0], ['l_id' => 4, 'avg' => 0.0],]