我有一個學生預訂表，列出了他預訂的所有老師。結構如下：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 關注
- 145 瀏覽
添加回答
舉報
0/150
提交
取消