我有一個包含 7 列的 (Examdata) 表：（Class_ID、Subject_ID、Student_ID、Subject_name、exam_days、exam_dates、exam_times）我想檢測第二天有考試和另一場考試的“student_id”，例如：| student_id | subject_name | exam_days | exam_date ||------------|--------------|-----------|-----------|| 1 | math | Sunday | 2/4/2019 || 1 | physical | Monday | 3/4/2019 |這里的 student_id="1" 連續幾天有兩次考試，也許 student_id 比我想檢測的還要多。注意：如果學生在周四 m 和周日有考試，則不應考慮，因為他們之間是周末。這是我的嘗試：<?php$con = mysqli_connect("localhost", "root", "", "Exams");$array1 = array();$n = "SELECT DISTINCT exam_dates FROM Examdata ORDER BY exam_dates";$queryarray1 = mysqli_query($con, $n) or die("Error in query: $queryarray1. ".mysqli_error());while ($row = mysqli_fetch_assoc($queryarray1)) { $array1[] = $row;}// print_r($array1);/* the output for this :Array ( [0] => Array ( [exam_dates] => 1440-04-02 ) [1] => Array ( [exam_dates] => 1440-04-03 ) [2] => Array ( [exam_dates] => 1440-04-04 ) [3] => Array ( [exam_dates] => 1440-04-05 ) [4] => Array ( [exam_dates] => 1440-04-06 ) [5] => Array ( [exam_dates] => 1440-04-08 ) [6] => Array ( [exam_dates] => 1440-04-09 ) [7] => Array ( [exam_dates] => 1440-04-10 ) [8] => Array ( [exam_dates] => 1440-04-11 ) [9] => Array ( [exam_dates] => 1440-04-12 ) [10] => Array ( [exam_dates] => 1440-04-13 ) [11] => Array ( [exam_dates] => 1440-04-15 ) [12] => Array ( [exam_dates] => 1440-04-16 ) [13] => Array ( [exam_dates] => 1440-04-17 ) [14] => Array ( [exam_dates] => 1440-04-18 ) [15] => Array ( [exam_dates] => 1440-04-20 ) )*/$b = json_encode($array1);$z = sizeof($array1);for ($i = 0; $i < $z; $i++) { $search = "SELECT * FROM Examdata e1 ( SELECT * }}消息錯誤：查詢錯誤：。您的 SQL 語法有錯誤；檢查與您的 MariaDB 服務器版本相對應的手冊，以獲取在第 2 行附近使用的正確語法（SELECT * from Examdata e2 where e1.exam_dates==[ && e2.exam_dates='-- 但我不知道如何在查詢中傳遞數組。
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 關注
- 193 瀏覽
添加回答
舉報
0/150
提交
取消