php - 在子查询中选择多行或逃避子查询

Question

我想知道如何实现查询，该查询选择按clientid列WHERE子句分组的时间列的总和，并从同一个表中除以月份和部门:

表格如下:

这里的目标是获得如下所示的最终结果:

结果 1.0

------------------------------
clientid | december| february
------------------------------
1        | 2:20    | 4:00
------------------------------
2        | 4:00    | 7:00
------------------------------ 

结果将按以下方式显示:

<table>
  <thead>
    <tr>
      <th>Clientes</th>
      <th>January</th>
      <th>February</th>                                                         
    </tr>
</thead>
<tbody>
<?php foreach($stmt as $row)
{   
echo"
<tr>
  <td>".$row['clientid']."</td>
  <td>".$row['january']."</td>
  <td>".$row['february']."</td>                               
</tr>"; }; ?>              
</tbody>
</table>  

在以下查询场景中:

SELECT t.clientid, t.department, 
                        (SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `time` ) ) ) FROM time_management WHERE YEAR(date)='$year' AND MONTH(date)= 12 AND department = '$department' GROUP by clientid  ) as january,
                        (SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `time` ) ) ) FROM time_management WHERE YEAR(date)='$year' AND MONTH(date)= 2 AND department = '$department' GROUP by clientid ) as february
                        FROM time_management t WHERE t.department = '$department' GROUP by t.clientid

我收到错误:SQLSTATE[21000]:基数违规:1242 子查询返回超过 1 行

因为子查询返回不止一行。

如果子查询中没有 GROUP BY，结果如下:

1   06:20:00    11:00:00                                            
2   06:20:00    11:00:00

所以我的问题是如何构建查询以获得结果1.0中显示的所需结果

非常欢迎任何帮助。谢谢!

Answer 1

不需要使用子查询。您可以使用条件聚合:

SELECT SUM( CASE WHEN MONTH(`date`) = 2 THEN TIME_TO_SEC( `time` )  ELSE 0 END) AS February,
       SUM( CASE WHEN MONTH(`date`) = 12 THEN TIME_TO_SEC( `time` )  ELSE 0 END) AS December
FROM time_management
WHERE t.department = '$department'
GROUP BY clientid

这样您就可以计算一年中每个月时间的SUM。