我希望要執行以下功能:

1. 讓在查詢money時可以多回傳兩個欄位
   • balance
   • subject
     balance是依照cost欄位直接做加減(但如果subjectid為-1則保留前筆資料)
     subject是依照subjectid欄位去subject資料庫的no去查找

SELECT
            m.*,
            (SELECT SUM(cost)
             FROM `money`
             WHERE id <= m.id AND (date LIKE ?) AND deletetime = '' AND type = ?) AS `balance`,
            s.no AS `subject`
        FROM `money` m
        LEFT JOIN `subject` s ON m.subjectid = s.id
        WHERE (m.date LIKE ?) AND m.deletetime = '' AND m.type = ?
        ORDER BY m.id

db架構圖如下

這是能做到的嗎? 還是一定要用一般的程式碼實現(因為讓程式去跑會讓網站有點卡)
原程式如下

$row=query($db,"SELECT*FROM `money` WHERE (`date` LIKE ?) AND `deletetime`='' AND `type`=?",[$_GET["year"]."%",$_GET["type"]]);
$data=[];
$balance=0;
for($i=0;$i<count($row);$i=$i+1){
    if($row[$i]["subjectid"]!=-1)
        $balance=$balance+$row[$i]["cost"];
    $data[]=[
        "id"=>$row[$i]["id"],
        "subject"=>query($db,"SELECT*FROM `subject` WHERE `id`=?",[$row[$i]["subjectid"]])[0]["no"]??-1,
        "subjectid"=>$row[$i]["subjectid"],
        "no"=>$row[$i]["no"],
        "type"=>$row[$i]["type"],
        "name"=>$row[$i]["name"],
        "description"=>$row[$i]["description"],
        "cost"=>$row[$i]["cost"],
        "costtype"=>$row[$i]["costtype"],
        "status"=>$row[$i]["status"],
        "date"=>$row[$i]["date"],
        "balance"=>$balance,
        "updatetime"=>$row[$i]["updatetime"]
    ];
}
$data=array_reverse($data);
echo(json_encode([
    "success"=>true,
    "data"=>$data
]));