当分数等于maxScore时，我如何在查询输出上创建第三列作为'Pass'或'Fail'
<?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
?>
使用变量:
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应用程序中做这个测试
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