mysql - 如何将mysql行转换为没有标签的列

Question

我有一个带有行的 mysql 表，它基本上是用户的进出时间，但它没有任何行是进出行的标记。

找出答案的唯一方法是为每个用户的每个日期计算最短和最长时间，然后在两列中选择它们。但是我很难做到这一点..请帮忙

My current data looks like this:

+-----------+--------------+-----------------------+
| "User Id" | Date         | "Time"                |
+-----------+--------------+-----------------------+
| userA     | "2019-04-22" | "2019-04-22 08:48:23" |
+-----------+--------------+-----------------------+
| userA     | "2019-04-22" | "2019-04-22 17:47:51" |
+-----------+--------------+-----------------------+
| userB     | "2019-04-22" | "2019-04-22 08:19:52" |
+-----------+--------------+-----------------------+
| userB     | "2019-04-22" | "2019-04-22 18:06:27" |
+-----------+--------------+-----------------------+
| userC     | "2019-04-22" | "2019-04-22 08:27:28" |
+-----------+--------------+-----------------------+
| userc     | "2019-04-22" | "2019-04-22 17:19:25" |
+-----------+--------------+-----------------------+

Desired Output is This:
+--------+--------------+-----------------------+-----------------------+
| Userid | Date         | InTime                | OutTime               |
+--------+--------------+-----------------------+-----------------------+
| userA  | "2019-04-22" | "2019-04-22 08:48:23" | "2019-04-22 17:47:51" |
+--------+--------------+-----------------------+-----------------------+
| userB  | "2019-04-22" | "2019-04-22 08:19:52" | "2019-04-22 18:06:27" |
+--------+--------------+-----------------------+-----------------------+
| userC  | "2019-04-22" | "2019-04-22 08:27:28" | "2019-04-22 17:19:25" |
+--------+--------------+-----------------------+-----------------------+

提前致谢。

Answer 1

将 min() 和 max() 与 group by 一起使用

select Userid , Date, min(`time`) as intime,max(`time`) as outtime
from tablename
group by Userid , Date