Mysql查询输出子查询之间的比较

Question

当分数等于 maxScore 时，如何在查询输出上创建第三列作为“通过”或“失败”

<?php

$database =& JFactory::getDBO(); 

//Declare Variables
$user = JFactory::getUser();
$id = $user->get('id');
$name = $user->get('name');

// Display quizzes
echo "</br>";
echo "<h1>";
echo "Quizzes History for : " ;
echo "<b>";
echo $name;
echo "</h1>";
echo "</b>";


echo "</br>";
echo "</br>";

$database->setQuery(" SELECT distinct qui.title AS Course_Name,   

(SELECT sum(score) 
 FROM jos_jquarks_quizzes_answersessions     

  WHERE score IS NOT NULL AND   quizsession_id = quizSession.id     
   AND status <> -1 ) AS score, 

( SELECT count(distinct question_id)  FROM jos_jquarks_quizzes_answersessions            
WHERE quizsession_id = quizSession.id ) AS maxScore,  


DATE_FORMAT(quizSession.finished_on,'%W, %M %e, %Y @ %h:%i %p') As Finished  FROM jos_jquarks_quizsession AS quizSession  

LEFT JOIN jos_jquarks_users_quizzes AS users_quizzes ON users_quizzes.id = quizSession.affected_id    
LEFT JOIN jos_jquarks_quizzes AS qui ON users_quizzes.quiz_id = qui.id     
LEFT JOIN jos_jquarks_quizzes_answersessions AS quizSessAns ON quizSessAns.quizsession_id = quizSession.id 
LEFT JOIN jos_jquarks_sessionwho AS sessionWho ON sessionWho.session_id = quizSession.id        
LEFT JOIN jos_jquarks_users_profiles AS users_profiles ON users_profiles.user_id = sessionWho.user_id 
LEFT JOIN jos_jquarks_profiles AS profiles ON profiles.id = users_profiles.profile_id   
WHERE sessionWho.user_id =  " .$id  ) ;

if (!$database->query()) { //write data and if error occurs alert
    echo "<script> alert('".$database->getErrorMsg()."'); </script>";
}
$tableStyle = "padding: 5px;border:1px solid black"; 
$tdStyle = "padding:5px"; 
$thStyle = "padding:7px";

$row = $database->loadRowList();
if ( 0<count($row)) {
    echo '<table style="', $tableStyle, '" cellpadding="7" cellspacing="7">'; // with echo, commas are slightly more effective than dots 
    echo '<tr><th style="', $thStyle, '" align=center>Quiz Title </th><th style="', $thStyle, '" align=center> Score </th>
    <th style="', $thStyle, '" align=center>Maximum Score </th>
<th style="', $thStyle, '" align=center>Finished On </th></tr>'; 

    $row = $database->loadRowList();
    foreach($row as $valuearray) {
        echo '<tr align="center">';

        foreach($valuearray as $field){
            echo '<td style="', $tdStyle, '" align=center>', $field, '</td>'; // note: there was an error here
        } // field
        echo '</tr>'; // note: apostrophs rather than " every time it is possible
    } // $valuearray
    echo '</table>'; 
} // if count
?>

Answer 1

使用变量：

SELECT DISTINCT qui.title AS Course_Name,

  @score := ( SELECT sum(score) FROM jos_jquarks_quizzes_answersessions     
     WHERE score IS NOT NULL AND quizsession_id = quizSession.id     
     AND status <> -1 ) AS score, 

  @maxScore := ( SELECT count(distinct question_id)
    FROM jos_jquarks_quizzes_answersessions            
    WHERE quizsession_id = quizSession.id ) AS maxScore,

  IF( @score = @maxScore, 'Pass', 'Fail' ) AS passOrFail

  ...

但我个人会在我的 PHP 应用程序中进行此测试。

Answer 2

SELECT
    x.CourseName,
    x.score,
    x.maxScore,
    x.Finished,
    CASE
        WHEN x.score = x.maxScore THEN 'Pass'
        ELSE 'Fail'
    END AS PassFail
FROM (
    SELECT 
        distinct qui.title AS Course_Name,   
        (   SELECT sum(score) 
            FROM jos_jquarks_quizzes_answersessions     
            WHERE score IS NOT NULL AND   quizsession_id = quizSession.id AND status <> -1 
        ) AS score, 
        (   SELECT count(distinct question_id)  
            FROM jos_jquarks_quizzes_answersessions 
            WHERE quizsession_id = quizSession.id 
        ) AS maxScore,  
        DATE_FORMAT(quizSession.finished_on,'%W, %M %e, %Y @ %h:%i %p') As Finished  
    FROM jos_jquarks_quizsession AS quizSession  
    LEFT JOIN jos_jquarks_users_quizzes AS users_quizzes ON users_quizzes.id = quizSession.affected_id    
    LEFT JOIN jos_jquarks_quizzes AS qui ON users_quizzes.quiz_id = qui.id     
    LEFT JOIN jos_jquarks_quizzes_answersessions AS quizSessAns ON quizSessAns.quizsession_id = quizSession.id 
    LEFT JOIN jos_jquarks_sessionwho AS sessionWho ON sessionWho.session_id = quizSession.id        
    LEFT JOIN jos_jquarks_users_profiles AS users_profiles ON users_profiles.user_id = sessionWho.user_id 
    LEFT JOIN jos_jquarks_profiles AS profiles ON profiles.id = users_profiles.profile_id   
    WHERE sessionWho.user_id = " . $id . "
    ) x