我有一个学生预订表，列出了他预订的所有老师。结构如下：Teacher Name Date start_time end_time hour rate现在我想列出预订如下我想计算学生支付给每位老师的总费用以及为每位老师预订的总小时数。我想用一个查询来做到这一点，我正在使用 php 和 mysql。 S. No Teacher's Name Date Class Start time Class End Time Hours Rate1. Amit Kumar 5-10-2019 8:00 A.M. 9:00 A.M. 1 2000.002. Amit Kumar 6-10-2019 9:00 A.M. 10:00 A.M. 1 5000.00Total 2 7000.003. Pawan Kumar 7-10-2019 10:00 A.M. 11:00 A.M. 1 4000.004. Pawan Kumar 8-10-2019 7:00 A.M. 8:00 A.M. 1 4000.00Total 2 8000.00......
2 回答
万千封印
TA贡献1891条经验 获得超3个赞
SELECT TeacherName, SUM(Hours), SUM(Rate) FROM TABLE GROUP BY TeacherName
我不认为您可以查询显示预订课程的详细部分，除非您要在 html 部分添加一些功能。
编辑 - - - - - - - - - - -
试试这个
<?php
$conn = new PDO('mysql:host=localhost', 'root', '');
$sel = $conn->query("SELECT * FROM db_try.TeachersBooking");
$cnt = 0;
$TeacherName = "";
$arrData = array();
while ($row = $sel->fetch(PDO::FETCH_ASSOC)) {
if($TeacherName != $row['TeacherName'])
{
$cnt = 0;
}
$TeacherName = $row['TeacherName'];
$arrData[$TeacherName][$cnt]['TeacherName'] = $row['TeacherName'];
$arrData[$TeacherName][$cnt]['Date'] = $row['Date'];
$arrData[$TeacherName][$cnt]['start_time'] = $row['start_time'];
$arrData[$TeacherName][$cnt]['end_time'] = $row['end_time'];
$arrData[$TeacherName][$cnt]['hour'] = $row['hour'];
$arrData[$TeacherName][$cnt]['rate'] = $row['rate'];
$cnt++;
}
?>
<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body>
<table cellspacing="20">
<tr>
<th>No</th>
<th>Teacher's Name</th>
<th>Date</th>
<th>Class start time</th>
<th>Class end time</th>
<th>Hours</th>
<th>Rate</th>
</tr>
<?php
$count = 1;
foreach ($arrData as $key => $Teacher) {
$TeacherRate = $TeacherHours = 0;
foreach ($Teacher as $key => $row) {
echo "<tr>
<td>".$count."</td>
<td>".$row['TeacherName']."</td>
<td>".$row['Date']."</td>
<td>".$row['start_time']."</td>
<td>".$row['end_time']."</td>
<td>".$row['hour']."</td>
<td>".$row['rate']."</td>
</tr>";
$TeacherHours += $row['hour'];
$TeacherRate += $row['rate'];
$count++;
}
echo "<tr>
<td colspan='5'>Total</td>
<td>".$TeacherHours."</td>
<td>".$TeacherRate."</td>
</tr>";
}
?>
</table>
</body>
</html>
结果：
绝地无双
TA贡献1946条经验 获得超4个赞
虽然，如果您将 start_time 和 end_time 声明为日期时间，这将是一件容易得多的事情。
在下面的代码行中，我必须以正常时间格式更改字符串，然后执行基本的时间求和：
SELECT TeacherName, SUM(Rate)
SEC_TO_TIME(SUM(TIME_TO_SEC
(TIMEDIFF(STR_TO_DATE(CONCAT(`date`, ' ',
REPLACE(`end_time`, '.', '')), '%Y-%m-%d %h:%i %p'), STR_TO_DATE(CONCAT(`date`, ' ',
REPLACE(`start_time`, '.', '')), '%Y-%m-%d %h:%i %p'))
)
)
) AS tot_time
FROM teachers
GROUP BY TeacherName
- 2 回答
- 0 关注
- 191 浏览
添加回答
举报
0/150
提交
取消