MySQL -Abfrage zum Filtern durch einen Summe () alias aus der Auswahlklausel ⇐ Php

[Anonymous]

Ich habe eine Abfrage und ich möchte das Ergebnis mit einer aliassen Spalte in meinem Wo -Zustand filtern. Ich habe gelesen, dass Sie es tun, aber ich habe noch keine alternative Möglichkeit, dies effizient zu tun, da ich an Tausenden von Datensätzen arbeite. < /p>

Code: Select all

$str = "SELECT
mainclass.id AS classid,
mainclass.code AS classcode,
Sum(CASE WHEN enroll.acctok = '1' AND enroll.assessed = '1'
THEN enroll.assessed
ELSE 0 end) AS assessedinclass,
Sum(enroll.validated = '1') AS validatedinclass,
section.name AS sectionname,
subject.code AS subcode,
subject.subdesc,
mainclass.units,
sched.name AS schedule,
mainclass.tutorial,
mainclass.dissolved,
mainclass.slots,
mainclass.ismother,
mergeclass.code AS mothercode,
mergeclass.id AS mothercodeid,
mergeclass.slots AS mothercodeslots,
mainclass.mergein,
mainclass.inst,
instructor.lname,
instructor.fname,
instructor.mname,
instructor.suffix
FROM
class AS mainclass
Left Join enrolldet ON mainclass.id = enrolldet.class
Left Join enroll ON enrolldet.enrollno = enroll.enrollno
Inner Join period ON mainclass.period = period.id
Inner Join section ON mainclass.section = section.id
Inner Join subject ON mainclass.subject = subject.id
Left Join sched ON mainclass.sched = sched.id
Left Join class AS mergeclass ON mainclass.mergein = mergeclass.id
Left Join instructor ON mainclass.inst = instructor.userid
Left Join course ON section.course = course.id
WHERE
(period.id = :period OR period.code = :period)";
if($level != ''){
$str .= " AND course.level = '".$level."'";
}
if($dept != ''){
$str .= " AND course.dept = '".$dept."'";
}
if($display != ''){
switch ($display) {
case 'open':
$str .= " AND mainclass.slots > assessedinclass";
break;
case 'dissolved':
$str .= " AND mainclass.dissolved = 1";
break;
case 'tutorial':
$str .= " AND mainclass.tutorial = 1";
break;
case 'closed':
$str .= " AND mainclass.slots