是否可以在同一查询的where子句中将子查询结果用作条件。我有这个sql代码。我想比较assessedClass,它是子查询和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";
1条答案
按热度按时间fcg9iug31#
要回答您的问题,可以使用
Having
条款,让你用后where
条款或者
但我更喜欢的是不要使用依赖子查询,而是使用join作为主要查询的子子句,如下所示