MySQL多行查询IF ELSE条件实现：学生批次数量判断输出

Got it, here's how you can solve this problem with a MySQL query using conditional logic, plus how to integrate it into your existing PHP code:

Core MySQL Query Logic #

To determine if a student has more than 2 unique batch IDs, we'll use COUNT(DISTINCT batchid) to tally unique batches per student, then use either a CASE statement or MySQL's IF() function to output the required status.

Option 1: Using CASE (more SQL-standard)

SELECT 
    studentid,
    CASE 
        WHEN COUNT(DISTINCT batchid) > 2 THEN 'more'
        ELSE 'one'
    END AS batch_status
FROM 
    enrollment
GROUP BY 
    studentid;

Option 2: Using IF() (more concise for MySQL)

SELECT 
    studentid,
    IF(COUNT(DISTINCT batchid) > 2, 'more', 'one') AS batch_status
FROM 
    enrollment
GROUP BY 
    studentid;

Note: We use COUNT(DISTINCT batchid) instead of plain COUNT(batchid) to avoid counting duplicate batch entries for the same student. This ensures we only count unique batches.

Example Output with Your Sample Data #

Running either query against your provided table will return:

Integrated PHP Code #

Here's how to plug this query into your existing PHP code:

include 'database/db_connect.php';

// Use the CASE version of the query (or swap in the IF version if preferred)
$sql = "
SELECT 
    studentid,
    CASE 
        WHEN COUNT(DISTINCT batchid) > 2 THEN 'more'
        ELSE 'one'
    END AS batch_status
FROM 
    enrollment
GROUP BY 
    studentid;
";

// Execute query and process results
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "Student ID: " . $row["studentid"]. " | Batch Status: " . $row["batch_status"]. "<br>";
    }
} else {
    echo "No student records found.";
}

mysqli_close($conn);

内容的提问来源于stack exchange，提问作者Dexter