时间戳

首页 > 解决方案 > where 子句中的 MYSQL 别名

php - where 子句中的 MYSQL 别名

问题描述

我有一个查询，我想在我的 WHERE 条件中使用 ALIASED 列过滤结果集，我读到你不能这样做，但我还没有其他方法可以有效地做到这一点，因为我正在处理数千条记录。 

$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 <= assessedinclass";
                break;
        }       
    }            
    $str .= "GROUP BY
                mainclass.id,
                mainclass.code";
    $str .= " ORDER BY subject.code, mainclass.id";
    return $this->_db->select($str,array(':period' => $period));

我试图使用 ASSESSEDINCLASS 过滤它，其中 mainclass.slots > ASSESSEDINCLASS 表示主题是开放的

而如果 mainclass.slots <= ASSESSEDINCLASS 它将被视为 CLOSED

标签： phpmysqldatabase

解决方案

你可以试试下面。编写一个外部选择，它将包含您处理过滤器所需的所有列。

select
    classid,
    classcode,
    assessedinclass,
    validatedinclass,
    sectionname,
    subcode,
    subdesc,
    units,
    schedule,
    tutorial,
    dissolved,
    slots,
    ismother,
    mothercode,
    mothercodeid,
    mothercodeslots,
    mergein,
    inst,
    lname,
    fname,
    mname,
    suffix 
from 
    (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);

您的最终查询将是 -

select
    <req_cols> 
 from
     (select 
          <agg_cols> 
      from 
          table 
      group by 
          <grpCols>)
where 
    <filter on agg cols>

推荐阅读