请我需要您的帮助来优化我的查询。我想从同一个表中获取 10 个不同的行数据。
这是查询之一
$query1 = mysql_query("SELECT m.Course_code AS 'Course', m.score, m.grade
FROM maintable AS m
INNER JOIN students AS s ON
m.matric_no = s.matric_no
INNER JOIN Courses AS c ON
m.Course_code = c.Course_code
WHERE m.matric_no = '".$matric_no."'
AND m.semester_name = 'hamattarn'
AND m.level = '100' ") or die (mysql_error());
$number_cols1 = mysql_num_fields($query1) ;
查询的这一部分正在为所有查询更改
AND m.semester_name = 'hamattarn'
AND m.level = '100' "
AND m.semester_name = 'rain'
AND m.level = '100' "
AND m.semester_name = 'hamattarn'
AND m.level = '200' "
AND m.semester_name = 'rain'
AND m.level = '200' "
AND m.semester_name = 'hamattarn'
AND m.level = '300' "
AND m.semester_name = 'rain'
AND m.level = '300' "
AND m.semester_name = 'hamattarn'
AND m.level = '400' "
AND m.semester_name = 'rain'
AND m.level = '400' "
AND m.semester_name = 'hamattarn'
AND m.level = '500' "
AND m.semester_name = 'rain'
AND m.level = '500' "
这是我通过查询能够实现的目标的图片。优化后,我仍然希望它看起来像这样。
感谢您的时间和耐心。
更新 - 这是我在优化之前显示 10 个查询中每个查询的行表的方式。请问如何使用新的优化查询实现此表类型结果?
echo "<table class='"altrowstable'" id = " bgcolor = gold >'n";
echo "<tr align=center>'n";
for ($i=0; $i<$number_cols10; $i++)
{
echo "<th>" . mysql_field_name($query10, $i). "</th>";
}
echo "</tr>'n";
while ($row = mysql_fetch_row($query10))
{
echo "<tr align=center>'n";
for ($i=0; $i<$number_cols10; $i++)
{
echo "<td>";
if (!isset($row[$i]))
{echo "NULL";}
else
{
echo "<b>".$row[$i]."</b>";
}
echo "</td>'n";
}
echo "</tr>'n";
}
echo "</table>";
echo"</span>" ;
谢谢
为什么不像这样:
WHERE m.semester_name IN ('rain', 'hamattarn')
AND m.level IN ('100', '200', '300', '400', '500'))
由于结果将混合在一起,因此应按预期顺序获取结果,例如:
ORDER BY m.semester_name, m.level
这将允许您在 PHP 中拆分结果,如下所示很常见:
$previous_semester = $previous_level = false;
foreach(mysql_fetch_array($query) as $row) {
if ($previous_semester == $row['semester_name']) {
// row semester is different from the previous one
echo $row['semester_name'];
}
if ($previous_level == $row['level']) {
// row level is different from previous one
echo $row['level'];
}
print_r($row);
$previous_level = $row['level'];
$previous_semester = $row['semester_name'];
}
您可以在 for 循环之前打开一个 HTML 表。当学期或级别发生变化时,您可以关闭上一个表并打开一个新表,为同一学期/级别的其余行添加标题,等等,等等......您可以在 for 循环之后关闭 HTML 表。
这有点棘手,但最终它会让你到达那里。
如果你真的想优化它,那么构建一个 karnaugh 映射 - 一个表,一个轴枚举每个可能的 semester_name 值,另一个访问每个可能的 level 值,然后标记要包含在查询中的交叉点。由此,您将能够找出最简单的查询来补充该数据。
但是,由于您没有指出要排除的值,并且您已经包含了 semester_name={rain, hamattarn} 和 level={100,200,300,400,500} 的所有可能组合,那么一个简单的解决方案是:
WHERE semester_name IN ('rain', 'hamattarn')
AND level in (100,200,300,400,500)
您需要在 where 子句中包含semester_name和级别,如果您
ORDER BY semester_name, level, course
只需通过 WHERE 子句获取所有可能的组合:
WHERE m.matric_no = :matric_no
AND (
m.semester_name = 'hamattarn'
OR m.semester_name = 'rain'
)
AND (
m.level = '100'
OR m.level = '200'
OR m.level = '300'
OR m.level = '400'
)