我正在獲取所有已批準但未存檔的員工的 ID、名字和姓氏。然后我循環這些結果并使用 ids 查詢其他表以收集一些計數數據。我嘗試了下面的代碼，但沒有得到預期的輸出。$queryEmp = " SELECT id, firstname, lastname FROM tbl_employee as e WHERE is_archive=0 and is_approved=1";$getQuery= $this->db->query($queryEmp);$result= $getQuery->result();foreach ($result as $key=> $value) { //echo "<pre>"; print_r($value); $day = "MONTH(date_of_created) = DATE(CURRENT_DATE())"; $group = "f_id IN (SELECT MAX(f_id) FROM tbl_fileStatus GROUP BY f_bankid)"; $condiion = "and ba.createdby='" . $value->id . "' and " . $day ." and " . $group; $query2 = " select (SELECT COUNT(c_id) FROM tbl_lead WHERE leadstatus='1' AND ".$day.") as confirmCount, (SELECT COUNT(f_id) FROM tbl_fileStatus as fs join tbl_bankdata as ba on ba.bank_id=fs.f_bankid WHERE fs.f_filestatus=1 " . $condiion . ") as disbursed, (SELECT COUNT(f_id) FROM tbl_fileStatus as fs join tbl_bankdata as ba on ba.bank_id=fs.f_bankid WHERE fs.f_filestatus=2 ".$condiion.") as filesubmit "; # code... $getQuery2= $this->db->query($query2); $result2[]=$getQuery2->result();}echo "<pre>";print_r(result2);$result看起來像這樣：Array ( [0] => stdClass Object ( [id] => 1 [firstname] => xyz [lastname] => xyz ) ...)第二個查詢輸出：Array ( [0] => Array ( [0] => stdClass Object ( [fallowCall] => 0 [confirmCount] => 0 [disbursed] => 0 [filesubmit] => 0 ) ) ...)我在這里添加了我的表結構和一些示例數據：https://www.db-fiddle.com/f/8MoWmKPuzTrrC3DQJsiX35/0這里有一些注意事項1）createdby是表的idtbl_employee2）lead_id在銀行表中是c_id表的tbl_lead3)f_bankid在tbl_fileStatus是bank_id表的tbl_bankdata
1 回答
肥皂起泡泡
TA貢獻1829條經驗 獲得超6個贊
實際上不需要創建額外的深度/復雜性來保存計數數據。此外，通過使用 LEFT JOIN 的組合來連接相關表并應用所需的條件規則，您只需訪問數據庫一次即可獲得所需的結果。毫無疑問，這將為您的應用程序提供卓越的效率。LEFT JOIN 使用起來很重要，這樣計數可以為零，而不會將員工排除在結果集中。
另外，我應該指出，您嘗試的查詢錯誤地將一個MONTH()值與一個DATE()值進行了比較——這永遠不會有好結果。:) 事實上，為了確保您的 sql 準確地將當前月份與當前年份分開，您還需要檢查 YEAR 值。
我推薦的sql：
SELECT
employees.id,
employees.firstname,
employees.lastname,
COUNT(DISTINCT leads.c_id) AS leadsThisMonth,
SUM(IF(fileStatus.f_filestatus = 1, 1, 0)) AS disbursedThisMonth,
SUM(IF(fileStatus.f_filestatus = 2, 1, 0)) AS filesubmitThisMonth
FROM tbl_employee AS employees
LEFT JOIN tbl_lead AS leads
ON employees.id = leads.createdby
AND leadstatus = 1
AND MONTH(leads.date_of_created) = MONTH(CURRENT_DATE())
AND YEAR(leads.date_of_created) = YEAR(CURRENT_DATE())
LEFT JOIN tbl_bankdata AS bankData
ON employees.id = bankData.createdby
LEFT JOIN tbl_fileStatus AS fileStatus
ON bankData.bank_id = fileStatus.f_bankid
AND MONTH(fileStatus.date_of_created) = MONTH(CURRENT_DATE())
AND YEAR(fileStatus.date_of_created) = YEAR(CURRENT_DATE())
AND fileStatus.f_id = (
SELECT MAX(subFileStatus.f_id)
FROM tbl_fileStatus AS subFileStatus
WHERE subFileStatus.f_bankid = bankData.bank_id
GROUP BY subFileStatus.f_bankid
)
WHERE employees.is_archive = 0
AND employees.is_approved = 1
GROUP BY employees.id, employees.firstname, employees.lastname
該SUM(IF())表達式是一種用于執行“條件計數”的技術?！熬酆蠑祿笔峭ㄟ^使用 GROUP BY 形成的，并且必須使用專門的“聚合函數”從這些集群/非平面數據集合中創建線性/平面數據。 fileStatus由于 GROUP BY 調用，數據有效地堆積在自身上。如果COUNT(fileStatus.f_filestatus)被調用，它將計算集群中的所有行。由于您希望區分f_filestatus = 1和f_filestatus = 2，IF()因此使用了一個語句。這與（為每個符合條件的出現加 1）做同樣的事情COUNT()，但它的不同之處在于COUNT()它不計算特定行（在集群范圍內）除非表達式IF()被滿足。 另一個例子。
這是一個 db fiddle 演示，對您提供的樣本數據進行了一些調整：https://www.db-fiddle.com/f/8MoWmKPuzTrrC3DQJsiX35/4 （結果集只會是“好”，而當前是今年 6 月.)
將上述字符串保存為 后$sql，您可以簡單地執行它并循環遍歷對象數組，如下所示：
foreach ($this->db->query($sql)->result() as $object) {
// these are the properties available in each object
// $object->id
// $object->firstname
// $object->lastname
// $object->leadsThisMonth
// $object->disbursedThisMonth
// $object->filesubmitThisMonth
}
- 1 回答
- 0 關注
- 126 瀏覽
添加回答
舉報
0/150
提交
取消