我有一個(gè)學(xué)生預(yù)訂表，列出了他預(yù)訂的所有老師。結(jié)構(gòu)如下：Teacher Name Date start_time end_time hour rate現(xiàn)在我想列出預(yù)訂如下我想計(jì)算學(xué)生支付給每位老師的總費(fèi)用以及為每位老師預(yù)訂的總小時(shí)數(shù)。我想用一個(gè)查詢來(lái)做到這一點(diǎn)，我正在使用 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 回答
萬(wàn)千封印
TA貢獻(xiàn)1891條經(jīng)驗(yàn) 獲得超3個(gè)贊
SELECT TeacherName, SUM(Hours), SUM(Rate) FROM TABLE GROUP BY TeacherName
我不認(rèn)為您可以查詢顯示預(yù)訂課程的詳細(xì)部分，除非您要在 html 部分添加一些功能。
編輯 - - - - - - - - - - -
試試這個(gè)
<?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>
結(jié)果：
絕地?zé)o雙
TA貢獻(xiàn)1946條經(jīng)驗(yàn) 獲得超4個(gè)贊
雖然，如果您將 start_time 和 end_time 聲明為日期時(shí)間，這將是一件容易得多的事情。
在下面的代碼行中，我必須以正常時(shí)間格式更改字符串，然后執(zhí)行基本的時(shí)間求和：
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 關(guān)注
- 142 瀏覽
添加回答
舉報(bào)
0/150
提交
取消