查询以获取employees列的值

zqdjd7g9 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(243)

我正在跟踪表订单中的所有员工数据。。。。
我需要获取所有雇员[column name:employeename]reatest[column:reatestdate]&hold values[column:holddate]
我想以以下格式显示这些信息：
员工姓名1:重新尝试：重新尝试值：保留：保留值
员工姓名2:重新尝试：重新尝试值：保留：保留值
例子：
chittaranjan:reattempet:20，hold:10
深呼吸：15，保持：5
我尝试了以下查询：

$sql = "SELECT employeename, DATE(reattemptdate) as date, 
COUNT(*) as count, 0 as Held FROM orders 
WHERE DATE(reattemptdate)  = CURDATE() 
GROUP BY employeename, date

UNION

SELECT employeename, DATE(holddate) as date, 
COUNT(*) as count, 1 as Held  FROM orders 
WHERE DATE(holddate)  = CURDATE() 
GROUP BY employeename, date
"; 
$results = $db_handle->runSelectQuery($sql); 
$numrowsresult =$results[0]['count'];

foreach ($results as $result) 
{
    if ($result['Held']==0){
      $header = " Reattempet ";
    } else {
      $header = " hold ";
    }
    echo $result['employeename']."<br>";
    echo $header.": ".$result['count']."<br>";
}

我得到如下输出：
chittaranjan reattempet：20岁
深度：15
chittaranjan等待：10
迪帕克控股公司：5
我需要对上述查询进行哪些更改以获得所需的结果。。。。
编辑-列结构：

保持：

雇员

:

mysql php mysqli

来源：https://stackoverflow.com/questions/52274328/query-to-get-the-employees-column-values

2条答案

按热度按时间

yxyvkwin1#

另一种方法是修改代码：

foreach ($results as $result) 
{
  if ($result['Held']==0){
    $new_array[$result['employeename']][0] = " Reattempet :" . $result['count'];
  } else {
    $new_array[$result['employeename']][1] = " hold :" . $result['count'];
  }
}
foreach ($new_aray as $name => $val)
{
  echo $name . " : " . $val[0] . " : " . $val[1];
}

赞(0）回复(0）举报 2021-06-21

jei2mxaa2#

让数据库来做这项工作，它是为它量身定做的
将查询更改为：

SELECT CONCAT(employeename,' : Reattempt : ', count_Reattempet, ' : Hold : ', count_Hold) AS concat_header
FROM
(
    SELECT employeename, 
           SUM(CASE WHEN Held = 'Reattempet' THEN 1 ELSE 0 END) AS count_Reattempet,
           SUM(CASE WHEN Held = 'Hold'       THEN 1 ELSE 0 END) AS count_Hold
    FROM
    (
        SELECT employeename, DATE(reattemptdate) as date, 'Reattempet' as Held FROM orders WHERE DATE(reattemptdate)  = CURDATE() 
        UNION
        SELECT employeename, DATE(holddate)      as date, 'Hold'       as Held  FROM orders WHERE DATE(holddate)  = CURDATE() 
    ) A
    GROUP BY employeename
) B

它将返回一列 concat_header 完全格式化如你所愿。

chittaranjan : Reattempet : 20 : hold : 10
Deepak Reattempet : 15 : hold : 5

那么你只需要用php来做这个

foreach ($results as $result) 
{
      echo $result['concat_header']."<br>";
}

赞(0）回复(0）举报 2021-06-21

我来回答

查询以获取employees列的值

2条答案

相关问题

热门标签

最新问答