通过php查询mysql将日期列转为了行,但是在输出结果时出现了多余的0,1,2,3....等动态数据,怎么优化?
php查询
```php
<?php
header('content-type:application/json');
//$month = $_GET['month'];
require '../connect/pdo_connect_db.php';
// 查询需要动态列转行的日期范围
$sql = "SELECT DISTINCT datetime FROM TABLEA WHERE datetime >= '2023-11-01'";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$dates = $stmt->fetchAll(PDO::FETCH_COLUMN);
// 生成静态的SQL语句
$selects = array_map(function($date) {
return "SUM(CASE datetime WHEN '{$date}' THEN score ELSE 0 END) AS '{$date}'";
}, $dates);
$selects = implode(',', $selects);
$sql = "SELECT localid, name, {$selects} FROM TABLEA GROUP BY localid";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
if ($result){
$json = json_encode(array(
"code" => 0,
"msg" => "success",
"count" => count($result),
"data" => $result
), JSON_UNESCAPED_UNICODE);
echo $json;
} else {
$json = json_encode(array(
"code" => 1,
"msg" => "failed",
"count" => count($result),
"data" => $result
), JSON_UNESCAPED_UNICODE);
echo $json;
}
?>
获取的json data如下,实际如何修改上述php查询,让结果不显示0,1,2,3.....(如果查询一个月的数据,这里会显示0到32)
```php
"2023-11-30": "0"
},
{
"0": "3002",
"1": "LEE",
"2": "0",
"3": "0",
"localid": "3002",
"name": "LEE",
"2023-11-29": "0",
"2023-11-30": "0"
},
{
"0": "333",
"1": "ZHANG",
"2": "0",
"3": "0",
"localid": 323
"name": "ZHANG",
"2023-11-29": "0",
"2023-11-30": "0"
},
......
]
}