如何查询员工详细信息并关联他们的绩效指标?

Question

我正在获取所有已批准但未存档的员工的 ID、名字和姓氏。 然后，我循环这些结果并使用 id 查询其他表以收集一些计数数据。

我尝试了下面的代码，但我没有得到预期的输出。

$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
)
)
...
)

我怎样才能产生正确的结果，将各个员工与其绩效指标联系起来？ 此结构之一：

Array (
[0] => stdClass Object (
[id] => 1
[firstname] => xyz
[lastname] => xyz
[somename] => (
[fallowCall] => 0
[confirmCount] => 0
[disbursed] => 0
[filesubmit] => 0
)
)
...
)

或者这个结构：

Array (
[0] => stdClass Object (
[id] => 1
[firstname] => xyz
[lastname] => xyz
[fallowCall] => 0
[confirmCount] => 0
[disbursed] => 0
[filesubmit] => 0
)
...
)

我在这里添加了我的表结构和一些示例数据：https://www.db-fiddle.com/f/8MoWmKPuzTrrC3DQJsiX35/0

这里有一些注意事项

1(createdby是表tbl_employee的 ID

2(银行表中的lead_id是表c_idtbl_lead

3(tbl_fileStatus中的f_bankid是表格bank_idtbl_bankdata

Answer 1

实际上，没有必要仅仅为了保存计数数据而创建额外的深度/复杂性。 此外，通过使用 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 形成的，并且必须使用专门的"聚合函数"从这些集群/非平面数据集合中创建线性/平面数据。 由于 GROUP BY 调用，fileStatus数据有效地堆积在自身上。 如果调用COUNT(fileStatus.f_filestatus)，它将计算集群中的所有行。 由于您希望区分f_filestatus = 1和f_filestatus = 2，因此使用IF()语句。 这与COUNT()执行的操作相同(为每个符合条件的匹配项添加 1(，但它与COUNT()的不同之处在于，除非满足IF()表达式，否则它不会计算特定行(在集群范围内(。 另一个例子。

下面是一个数据库小提琴演示，对提供的示例数据进行了一些调整： 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
}