我正在獲取所有已批準(zhǔn)但未存檔的員工的 ID、名字和姓氏。然后我循環(huán)這些結(jié)果并使用 ids 查詢其他表以收集一些計數(shù)數(shù)據(jù)。我嘗試了下面的代碼，但沒有得到預(yù)期的輸出。$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 ) ) ...)我在這里添加了我的表結(jié)構(gòu)和一些示例數(shù)據(jù)：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貢獻(xiàn)1829條經(jīng)驗(yàn) 獲得超6個贊
實(shí)際上不需要創(chuàng)建額外的深度/復(fù)雜性來保存計數(shù)數(shù)據(jù)。此外，通過使用 LEFT JOIN 的組合來連接相關(guān)表并應(yīng)用所需的條件規(guī)則，您只需訪問數(shù)據(jù)庫一次即可獲得所需的結(jié)果。毫無疑問，這將為您的應(yīng)用程序提供卓越的效率。LEFT JOIN 使用起來很重要，這樣計數(shù)可以為零，而不會將員工排除在結(jié)果集中。
另外，我應(yīng)該指出，您嘗試的查詢錯誤地將一個MONTH()值與一個DATE()值進(jìn)行了比較——這永遠(yuǎn)不會有好結(jié)果。:) 事實(shí)上，為了確保您的 sql 準(zhǔn)確地將當(dāng)前月份與當(dāng)前年份分開，您還需要檢查 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())表達(dá)式是一種用于執(zhí)行“條件計數(shù)”的技術(shù)?！熬酆蠑?shù)據(jù)”是通過使用 GROUP BY 形成的，并且必須使用專門的“聚合函數(shù)”從這些集群/非平面數(shù)據(jù)集合中創(chuàng)建線性/平面數(shù)據(jù)。 fileStatus由于 GROUP BY 調(diào)用，數(shù)據(jù)有效地堆積在自身上。如果COUNT(fileStatus.f_filestatus)被調(diào)用，它將計算集群中的所有行。由于您希望區(qū)分f_filestatus = 1和f_filestatus = 2，IF()因此使用了一個語句。這與（為每個符合條件的出現(xiàn)加 1）做同樣的事情COUNT()，但它的不同之處在于COUNT()它不計算特定行（在集群范圍內(nèi)）除非表達(dá)式IF()被滿足。 另一個例子。
這是一個 db fiddle 演示，對您提供的樣本數(shù)據(jù)進(jìn)行了一些調(diào)整：https://www.db-fiddle.com/f/8MoWmKPuzTrrC3DQJsiX35/4 （結(jié)果集只會是“好”，而當(dāng)前是今年 6 月.)
將上述字符串保存為 后$sql，您可以簡單地執(zhí)行它并循環(huán)遍歷對象數(shù)組，如下所示：
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 關(guān)注
- 129 瀏覽
添加回答
舉報
0/150
提交
取消