mysql 按多个表分组

Question

表名 staff1---   --------  -------id    login_id  checkin---   -------- --------1        1     15:08:202        1     15:10:563        1     16:49:06表名 staff1out---   --------  -------id    login_id  checkout---   -------- --------1        1     15:10:412        1     15:11:143        1     16:54:09下表我想使用 php mysql 像这样显示-------      --------   ---------checkin      checkout    Hours --------    ---------   ---------15:08:20     15:10:41    Here time calculation15:10:56     15:11:14    Here time calculation16: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>      

Answer 1

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 |

    +----------+----------+   

Answer 2

尝试使用此查询。

SELECT *,timediff(checkout,checkin)hour FROM (

(SELECT s.login_id,s.checkin from staff1 as s)as g,

(SELECT b.checkout from staff2 as b) as e

)group by login_id

Answer 3

我更喜欢这个：

SELECT staff1.login_id

     , staff1.checkin

     , staff2.checkout

     , ROUND(TIMESTAMPDIFF(MINUTE, staff1.checkin, staff2.checkout)/60, 2) as hours 

  FROM staff1 

  LEFT 

  JOIN staff2 

    ON staff1.login_id = staff2.login_id

使用此查询，如果用户只签入但未签出，您也会得到一行。