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

cuxqih21 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(505)

我正在获取所有已批准但未存档的员工的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` 表的id `tbl_employee` 2)  `lead_id` 在银行的table上是 `c_id` 在table上 `tbl_lead` 3)  `f_bankid` 在 `tbl_fileStatus` 是 `bank_id` 在table上 `tbl_bankdata`

sql Join php codeigniter multidimensional-array

来源：https://stackoverflow.com/questions/61977036/how-to-query-employee-details-and-relate-their-performance-metrics

1条答案

按热度按时间

hs1ihplo1#

实际上不需要创建额外的深度/复杂性来保存计数数据。此外，通过使用左连接的组合来连接相关表并应用所需的条件规则，只需访问数据库一次，就可以获得所需的结果。毫无疑问，这将为您的应用程序提供更高的效率。使用左连接很重要，这样计数就可以为零，而不会将雇员排除在结果集中。
另外，我应该指出，您尝试的查询错误地比较了 MONTH() 对a的价值 DATE() 价值——这永远不会有好的结局。：）事实上，为了确保sql准确地将当前月份与当前年份隔离开来，还需要检查年份值。
我推荐的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 由于GROUPBY调用，数据有效地堆积在自身上。如果 COUNT(fileStatus.f_filestatus) 调用时，它将对集群中的所有行进行计数。既然你想区分 f_filestatus = 1 以及 f_filestatus = 2 ，安 IF() 使用语句。这和 COUNT() （为每个符合条件的事件添加1），但与 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
}

赞(0）回复(0）举报 2021-07-26

我来回答

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

1条答案

相关问题

热门标签

最新问答