php - MySQL SELECT WHERE 多个值

Question

我有这样一张 table

创建谷歌图表，所以我需要几个数据库查询来创建 JSON。一切都已完成，但很难进行数据库查询。

1. 解决了这个问题。
2. 需要选择所有计费和所有非计费小时数，按日期分组的总和。因此，要获得每天的计费和非计费小时数总和。

目前我有一个查询，

$json = array();
$sql = "SELECT SUM(TIME_TO_SEC(`hours`)) as seconds,date FROM `am_am2_time_tracking` WHERE is_billable = 1 GROUP BY date";
$result = $conn->query($sql);
while($row = $result->fetch_assoc())
{   
    $bil_hours = floatval($row['seconds'] / 60 / 60);
    $json[] = array('date'=>$row['date'],'bill_hours'=>$bil_hours);
}
echo json_encode($json);

这给了我这个 json:

[{"date":"2016-02-03","bill_hours":12.5},{"date":"2016-02-04","bill_hours":6},{"date":"2016-02-12","bill_hours":3},{"date":"2016-02-13","bill_hours":4},{"date":"2016-02-18","bill_hours":1.25},{"date":"2016-02-19","bill_hours":1},{"date":"2016-02-26","bill_hours":1.75},{"date":"2016-02-27","bill_hours":0.5}]

但不是创建新查询，而是寻找更好的方法将两个小时都放在一个 json 中。

Answer 1

您是否尝试进行以下查询

$sql = "SELECT SUM(TIME_TO_SEC(`hours`)) as seconds,date FROM `am_am2_time_tracking` GROUP BY date, is_billable";