php mysql 在同一查询中的 WHERE 子句中使用子查询结果

Question

是否可以在同一查询的 WHERE 子句中使用子查询结果作为条件。我有这个sql代码。我想比较子查询中的结果 ASSESSEDINCLASS 和 C.SLOTS 以确定插槽是否已满。这甚至可以在单个查询中实现吗？提前致谢

        $str = "SELECT
                c.id,
                c.code AS classcode,
                section.name AS sectionname,
                subject.code,
                subject.subdesc,
                c.units,
                sched.name AS schedule,
                c.slots,
                c.dissolved,
                c.tutorial,
                c.inst,
                instructor.lname,
                instructor.fname,
                instructor.mname,
                instructor.suffix,
                (SELECT
                    Count(e.enrollno)
                    FROM
                    enrolldet AS e
                    Inner Join enroll ON e.enrollno = enroll.enrollno
                    Inner Join class ON e.class = class.id
                    WHERE
                    enroll.validated =  '1' AND
                    class.id =  c.id) as validatedinclass,
                (SELECT
                    Count(e.enrollno)
                    FROM
                    enrolldet AS e
                    Inner Join enroll ON e.enrollno = enroll.enrollno
                    Inner Join class ON e.class = class.id
                    WHERE
                    enroll.assessed = '1' AND
                    class.id =  c.id) as assessedinclass,
                (SELECT
                    q.id
                    FROM
                        merged
                        Inner Join class AS q ON merged.mothercode = q.id
                    WHERE
                        merged.mergefrom = c.id) AS mergedto_mothercode
            FROM
                class AS c
                Left Join sched ON c.sched = sched.id
                Left Join section ON c.section = section.id
                Left Join subject ON c.subject = subject.id
                Left Join instructor ON c.inst = instructor.userid
                Left Join course ON section.course = course.id
                Inner Join period ON c.period = period.id
            WHERE
                (period.id =  '".$period."' OR period.code = '".$period."')";
    if($status == 'open'){
        $str .= " AND c.slots < assessedinclass";
    }
    $str .= " ORDER BY subject.subdesc";

Answer 1

要回答你的问题，是的，你可以使用 Having 子句，让你在 where 子句之后使用它

having assessedinclass = someval

或者

$str .= " HAVING c.slots < assessedinclass";

但我更喜欢不要使用依赖子查询，而是将这些作为子句连接到您的主查询中，如下所示

SELECT ...., 
       COALESCE(aic.assessedinclass, 0) AS assessedinclass, 
FROM   class AS c 
       JOINS....
       LEFT JOIN (SELECT class.id, 
                         COUNT(e.enrollno) AS assessedinclass 
                  FROM   enrolldet AS e 
                         INNER JOIN enroll 
                                 ON e.enrollno = enroll.enrollno 
                         INNER JOIN class 
                                 ON e.class = class.id 
                  WHERE  enroll.assessed = '1' 
                  GROUP  BY class.id) aic 
              ON aic.id = c.id 
WHERE  ( period.id = '".$period."' OR period.code = '".$period."' ) 
AND c.slots < COALESCE(aic.assessedinclass, 0)