时间戳

首页 > 解决方案 > mysql 按多个表分组

php - mysql 按多个表分组

问题描述

表名 staff1

---   --------  -------
id    login_id  checkin
---   -------- --------
1        1     15:08:20
2        1     15:10:56
3        1     16:49:06

表名 staff1out

---   --------  -------
id    login_id  checkout
---   -------- --------
1        1     15:10:41
2        1     15:11:14
3        1     16:54:09

下表我想使用 php mysql 像这样显示

-------      --------   ---------
checkin      checkout    Hours 
--------    ---------   ---------
15:08:20     15:10:41    Here time calculation
15:10:56     15:11:14    Here time calculation
16:49:06     16:54:09    Here time calculation

总小时数 = 总小时数

它是我的代码

<?php
    include_once("connection.php");
    $result = mysqli_query($mysqli, "SELECT staff1.checkin, staff1out.checkout, ROUND(TIMESTAMPDIFF(MINUTE, staff1.checkin, staff1out.checkout)/60, 2) as hours FROM staff1, staff1out ");
?>
<div class="container-fluid" style="margin-top:30px;">
  <div class="row">
    <div class="col-lg-12">
      <div class="table-responsive" style="overflow-x: unset;">
        <table class="table table-bordered table-striped">
          <tr bgcolor='transparent'>
            <td>CHECK IN
            </td>
            <td>CHECK OUT
            </td>
            <td>Hours
            </td>
          </tr>
<?php
    while($res = mysqli_fetch_array($result)) {     
        echo "<tr>";
        echo "<td>".$res['checkin']."</td>";
        echo "<td>".$res['checkout']."</td>";   
        echo "<td>".$res['hours']."</td>"; 
    }
?>
        </table>
      </div>
    </div>
  </div>
</div>

标签： phpmysql

解决方案

DROP TABLE IF EXISTS staff1;

CREATE TABLE staff1
(id SERIAL PRIMARY KEY 
,login_id INT NOT NULL
,checkin TIME NOT NULL
);

INSERT INTO staff1 VALUES
(1,1,'15:08:20'),
(2,1,'15:10:56'),
(3,1,'16:49:06');

DROP TABLE IF EXISTS staff1out;

CREATE TABLE staff1out
(id SERIAL PRIMARY KEY 
,login_id INT NOT NULL
,checkout TIME NOT NULL
);

INSERT INTO staff1out VALUES
(1,1,'15:10:41'),
(2,1,'15:11:14'),
(3,1,'16:54:09');

SELECT x.*
     , MIN(y.checkout) checkout
     , TIMEDIFF(MIN(y.checkout),x.checkin) delta 
  FROM staff1 x 
  LEFT 
  JOIN staff1out y 
    ON y.login_id = x.login_id 
   AND y.checkout > x.checkin 
 GROUP 
    BY x.id;
+----+----------+----------+----------+----------+
| id | login_id | checkin  | checkout | delta    |
+----+----------+----------+----------+----------+
|  1 |        1 | 15:08:20 | 15:10:41 | 00:02:21 |
|  2 |        1 | 15:10:56 | 15:11:14 | 00:00:18 |
|  3 |        1 | 16:49:06 | 16:54:09 | 00:05:03 |
+----+----------+----------+----------+----------+

在 PHP 中计算总时间可能是最简单的，但如果你想在 MySQL 中计算，可能看起来像这样......

SELECT login_id
     , SEC_TO_TIME(SUM(TIME_TO_SEC(delta))) total
  FROM 
   ( 
SELECT x.*
     , MIN(y.checkout) checkout
     , TIMEDIFF(MIN(y.checkout),x.checkin) delta 
  FROM staff1 x 
  LEFT 
  JOIN staff1out y 
    ON y.login_id = x.login_id 
   AND y.checkout > x.checkin 
 GROUP 
    BY x.id
   ) a
 GROUP 
    BY login_id;
    +----------+----------+
    | login_id | total    |
    +----------+----------+
    |        1 | 00:07:42 |
    +----------+----------+

推荐阅读