如何添加匹配MySQL查询的小时？

发布于 2025-02-03 08:05:26 字数 3625 浏览 2 评论 0原文

数据（表名：T1）：

教师	主题	日	星期三
艾伯特·布鲁（Albert	Blue）	10:00	艾伯特
·布鲁（Albert	Blue）	星期三	12:00
布兰登·	红色	星期二	09:00
布兰登·	雷德（Brandon Red）	星期二	11:00
艾伯特·塞兰（ Albert	Cyan）	星期一	08:30
Albert	Cyan	星期一	10:30
Claudia	Gray	星期四	08:00
Claudia	Gray	星期四	10:00
Albert	Pink	Friday	13:00
Albert	Pink	星期五	14:30
Martha	Green	星期三	12:00
Martha	Green	星期三	14:00
Albert	Yellow	星期五	11:00
Albert	Yellow	星期五	12:30，

因为可以是可以看出，有一个主题的开始小时的记录，另一个是同一主题的完成时间的记录（数据是这样的）。我打算知道的是特定老师为“艾伯特”说的每周小时数。

查询：

$result = mysqli_query($link, "SELECT SUBTIME(max(Hour), min(Hour)) AS TeachTime, Subject FROM T1 WHERE Teachers LIKE '%Albert%' GROUP BY Subject ORDER BY Subject ASC") or die(mysqli_error($link));
if (mysqli_num_rows($result)!==0) {
echo "Weekly teaching time: ";
while($row = mysqli_fetch_array($result)){
echo $row['TeachTime']." Hrs. ";
}
}

输出：

每周教学时间：02：00小时。 02:00小时。 01:30小时。 01:30 Hrs.

Desired output:

Weekly teaching time: 07:00 Hrs.

As you can see, I don't know how to perform the addition of every resulting amount of hours.我该如何实现？

我还尝试了的组，但结果很奇怪……不是添加结果。

原文

Data (Table name: T1):

Teacher	Subject	Day	Hour
Albert	Blue	Wednesday	10:00
Albert	Blue	Wednesday	12:00
Brandon	Red	Tuesday	09:00
Brandon	Red	Tuesday	11:00
Albert	Cyan	Monday	08:30
Albert	Cyan	Monday	10:30
Claudia	Gray	Thursday	08:00
Claudia	Gray	Thursday	10:00
Albert	Pink	Friday	13:00
Albert	Pink	Friday	14:30
Martha	Green	Wednesday	12:00
Martha	Green	Wednesday	14:00
Albert	Yellow	Friday	11:00
Albert	Yellow	Friday	12:30

As it can be seen, there is a record for the starting hour of a Subject and another for the finishing time of the same Subject (data comes like that).
What I intend to know is the weekly amount of hours dedicated to classes by a specific teacher, let's say for "Albert".

Query:

$result = mysqli_query($link, "SELECT SUBTIME(max(Hour), min(Hour)) AS TeachTime, Subject FROM T1 WHERE Teachers LIKE '%Albert%' GROUP BY Subject ORDER BY Subject ASC") or die(mysqli_error($link));
if (mysqli_num_rows($result)!==0) {
echo "Weekly teaching time: ";
while($row = mysqli_fetch_array($result)){
echo $row['TeachTime']." Hrs. ";
}
}

Output:

Weekly teaching time: 02:00 Hrs. 02:00 Hrs. 01:30 Hrs. 01:30 Hrs.

Desired output:

Weekly teaching time: 07:00 Hrs.

As you can see, I don't know how to perform the addition of every resulting amount of hours. How can I achieve that?

I have also tried GROUP BY Teachers but results are weird... not the addition result.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

多情癖 2025-02-10 08:05:26

continue

You know how to get this:

mysql> select teacher, day, min(hour), max(hour) 
from T1 where teacher = 'Albert' group by teacher, day;
+---------+-----------+-----------+-----------+
| teacher | day       | min(hour) | max(hour) |
+---------+-----------+-----------+-----------+
| Albert  | Wednesday | 10:00:00  | 12:00:00  |
| Albert  | Monday    | 08:30:00  | 10:30:00  |
| Albert  | Friday    | 11:00:00  | 14:30:00  |
+---------+-----------+-----------+-----------+

And you can use TIMESTAMPDIFF() to calculate the minutes:

mysql> select teacher, day, 
timestampdiff(minute, min(hour), max(hour)) as minutes 
from T1 where teacher = 'Albert' group by teacher, day;
+---------+-----------+---------+
| teacher | day       | minutes |
+---------+-----------+---------+
| Albert  | Wednesday |     120 |
| Albert  | Monday    |     120 |
| Albert  | Friday    |     210 |
+---------+-----------+---------+

Now wrap that as a subquery in another aggregation query:

mysql> select teacher, sum(minutes) as total_minutes 
from (
  select teacher, day, 
  timestampdiff(minute, min(hour), max(hour)) as minutes 
  from T1 where teacher = 'Albert' group by teacher, day) as t 
group by teacher;
+---------+---------------+
| teacher | total_minutes |
+---------+---------------+
| Albert  |           450 |
+---------+---------------+

回复收藏 0 原文

悲喜皆因你 2025-02-10 08:05:26

This is the code I end up using thanks to the guidance of Bill Karwin and some more research:

$result = mysqli_query($link, "SELECT *,
SEC_TO_TIME(SUM(TIME_TO_SEC(WeekTime))) AS ProfTime FROM
(SELECT *,
SUBTIME(max(Hour), min(Hour)) AS WeekTime FROM T1
WHERE Teacher LIKE '%Albert%' GROUP BY Teacher, Subject) AS C")
or die(mysqli_error($link));
if (mysqli_num_rows($result)!==0) {
echo "Weekly teaching time: ";
while($row = mysqli_fetch_array($result)){
echo date('H:i', strtotime($row['ProfTime']))." Hrs. <br>";
}}

Bill's answer had made me realize that you can query results from an embedded query, eg:

Query2 over results of (Query1 over data)

Then, as results of query1 were in time format (HH:MM), I should get a total of the addition of all the instances retrieved, ie, 01:00 + 02:00 + 01:30 = 04:30

The addition has to be performed in query2 and我已经在 tutorialSpoint 选择SEC_TO_TIME（sum（time_to_sec（time）））作为Total Time执行此操作。

最后，由于结果出现在格式 hh：mm：ss 中，而且我只需要 hh：mm 我将php转换应用于输出：date（date）（ 'H：i'，strtotime（$ row ['totaltime']））。

This is the code I end up using thanks to the guidance of Bill Karwin and some more research:

$result = mysqli_query($link, "SELECT *,
SEC_TO_TIME(SUM(TIME_TO_SEC(WeekTime))) AS ProfTime FROM
(SELECT *,
SUBTIME(max(Hour), min(Hour)) AS WeekTime FROM T1
WHERE Teacher LIKE '%Albert%' GROUP BY Teacher, Subject) AS C")
or die(mysqli_error($link));
if (mysqli_num_rows($result)!==0) {
echo "Weekly teaching time: ";
while($row = mysqli_fetch_array($result)){
echo date('H:i', strtotime($row['ProfTime']))." Hrs. <br>";
}}

Bill's answer had made me realize that you can query results from an embedded query, e.g.:

Query2 over results of (Query1 over data)

Then, as results of query1 were in time format (HH:MM), I should get a total of the addition of all the instances retrieved, i.e., 01:00 + 02:00 + 01:30 = 04:30

The addition has to be performed in query2 and I have found on tutorialspoint that the query SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(Time))) AS TotalTime does that.

Finally, as the result comes in the the format HH:MM:SS and I only need HH:MM I applied a php convertion to the output: date('H:i', strtotime($row['TotalTime'])).

回复收藏 0 原文

~没有更多了~