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

Question

我正在获取所有已批准但未存档的员工的 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_employee

2）lead_id在银行表中是c_id表的tbl_lead

3)f_bankid在tbl_fileStatus是bank_id表的tbl_bankdata

Answer 1

实际上不需要创建额外的深度/复杂性来保存计数数据。此外，通过使用 LEFT JOIN 的组合来连接相关表并应用所需的条件规则，您只需访问数据库一次即可获得所需的结果。毫无疑问，这将为您的应用程序提供卓越的效率。LEFT JOIN 使用起来很重要，这样计数可以为零，而不会将员工排除在结果集中。另外，我应该指出，您尝试的查询错误地将一个MONTH()值与一个DATE()值进行了比较——这永远不会有好结果。:) 事实上，为了确保您的 sql 准确地将当前月份与当前年份分开，您还需要检查 YEAR 值。我推荐的sql：SELECT&nbsp; &nbsp; employees.id,&nbsp; &nbsp; employees.firstname,&nbsp; &nbsp; employees.lastname,&nbsp; &nbsp; COUNT(DISTINCT leads.c_id) AS leadsThisMonth,&nbsp; &nbsp; SUM(IF(fileStatus.f_filestatus = 1, 1, 0)) AS disbursedThisMonth,&nbsp; &nbsp; SUM(IF(fileStatus.f_filestatus = 2, 1, 0)) AS filesubmitThisMonthFROM tbl_employee AS employeesLEFT JOIN tbl_lead AS leads&nbsp; &nbsp; ON employees.id = leads.createdby&nbsp; &nbsp; &nbsp; &nbsp; AND leadstatus = 1&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; AND MONTH(leads.date_of_created) = MONTH(CURRENT_DATE())&nbsp; &nbsp; &nbsp; &nbsp; AND YEAR(leads.date_of_created) = YEAR(CURRENT_DATE())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;LEFT JOIN tbl_bankdata AS bankData&nbsp; &nbsp; ON employees.id = bankData.createdbyLEFT JOIN tbl_fileStatus AS fileStatus&nbsp; &nbsp; ON bankData.bank_id = fileStatus.f_bankid&nbsp; &nbsp; &nbsp; &nbsp; AND MONTH(fileStatus.date_of_created) = MONTH(CURRENT_DATE())&nbsp; &nbsp; &nbsp; &nbsp; AND YEAR(fileStatus.date_of_created) = YEAR(CURRENT_DATE())&nbsp; &nbsp; &nbsp; &nbsp; AND fileStatus.f_id = (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT MAX(subFileStatus.f_id)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM tbl_fileStatus AS subFileStatus&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE subFileStatus.f_bankid = bankData.bank_id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GROUP BY subFileStatus.f_bankid&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )WHERE employees.is_archive = 0&nbsp; &nbsp; AND employees.is_approved = 1GROUP 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) {&nbsp; &nbsp; // these are the properties available in each object&nbsp; &nbsp; // $object->id&nbsp; &nbsp; // $object->firstname&nbsp; &nbsp; // $object->lastname&nbsp; &nbsp; // $object->leadsThisMonth&nbsp; &nbsp; // $object->disbursedThisMonth&nbsp; &nbsp; // $object->filesubmitThisMonth}