2 回答
TA贡献1865条经验 获得超7个赞
我会给你一个有效的解决方案,并尝试考虑一个更好/更优化的解决方案。
所以我们需要做的是在student_id上将Table与自己连接起来,然后我们需要使用DATEDIFF函数检查exam1是否在exam2之后1天到来。
这是 SQL 查询:
SELECT examData1.student_id, examData1.subject_name as first_subject_name, examData2.subject_name as next_subject_name , examData1.exam_days as first_exam_day, examData2.exam_days as next_exam_day, examData1.exam_date, examData2.exam_date
FROM Examdata as examData1, Examdata as examData2
WHERE examData1.student_id = examData2.student_id AND DATEDIFF(examData2.exam_date,examData1.exam_date) = 1
检查此链接以测试查询。
注意:您应该检查 SQL 语法导致您收到语法错误的错误。
TA贡献1816条经验 获得超6个赞
按日期顺序获取考试日期,然后您可以在PHP中使用这样的东西,或者您的解决方案需要“纯SQL”?
$currentStudent = 0;
$lastCheckedDate = 0;
$listOfStudentsWithConsecutiveDates = [];
while( $row = mysqli_fetch_array( $result ) ) {
if( $currentStudent == $row['student_id'] ) {
if( isset($listOfStudentsWithConsecutiveDates[ $row['student_id'] ]) ) continue;
$datediff = strtotime( $row['exam_date'] ) - $lastCheckedDate;
if( round($datediff / (60 * 60 * 24)) == 1 ) {
$listOfStudentsWithConsecutiveDates[ $row['student_id'] ] = $row;
);
} else {
$currentStudent = $row['student_id'];
$lastCheckedDate = strtotime( $row['exam_date'] );
}
}
你会得到一个数组
$listOfStudentsWithConsecutiveDates[ student_id ] = first exam which is consecutive to another one for this student
- 2 回答
- 0 关注
- 143 浏览
添加回答
举报