mysql - 子查询不访问父查询别名

Question

SELECT *,
`batch`.`batch_id` as `batchId`,
`batch`.`center_id` as `centerId`,
`batch`.`scheme_id` as `schemeId`,
`batch`.`batch_start_date` as `BatchStartDate`,
(SELECT COUNT(`attendance_count`) AS `eligible_for_assessment` FROM 
     (SELECT COUNT(`mark_attendance`) AS `attendance_count` FROM `dailyattendance` 
      WHERE `mark_attendance`="p" AND `scheme_id`=`schemeId` AND `batch_id`=`batchId`
      AND `center_id`=`centerId` GROUP BY `candidate_id`) AS `sfkjsd`
      WHERE `attendance_count` > 6
)
from `batch`

在此子查询中，它返回错误“‘where 子句’中的未知列‘schemeId’”。在mysql子查询中访问第三级子查询中的父参数？

(SELECT COUNT(`attendance_count`) AS `eligible_for_assessment` FROM 
     (SELECT COUNT(`mark_attendance`) AS `attendance_count` FROM `dailyattendance` 
      WHERE `mark_attendance`="p" AND `scheme_id`=1 AND `batch_id`=2
      AND `center_id`=3 GROUP BY `candidate_id`) AS `sfkjsd`
      WHERE `attendance_count` > 6
)

此查询返回一个月内出席超过 6 天的候选人总数。我需要从父选择查询动态传递此“schemeId”。

(SELECT COUNT(*) from `enrolment` WHERE
`enrolment`.`batch_id`=`batchId` AND `enrolment`.`center_id`=`centerId`
AND `enrolment`.`ew_enrolled_for_scheme`=`schemeId`) as `enrolled_student_count`

在这个查询中，我使用父选择器schemeId和其他选择器，它给了我结果计数。但是如果我在像上面这样的子查询中的 FROM (SELECT....) 中放置另一个子查询，我提到它会给我错误。

Answer 1

scheme_id=schemeId

应该是

scheme_id=批处理.scheme_id

和

batch_id=batchId

应该是

batch_id=batch.batch_id

试试这个代码

SELECT *,
`batch`.`batch_id` as `batchId`,
`batch`.`center_id` as `centerId`,
`batch`.`scheme_id` as `schemeId`,
`batch`.`batch_start_date` as `BatchStartDate`,
(SELECT COUNT(`attendance_count`) AS `eligible_for_assessment` FROM 
     (SELECT COUNT(`mark_attendance`) AS `attendance_count` FROM `dailyattendance` 
      WHERE `mark_attendance`="p" AND `scheme_id`=`batch`.`scheme_id` AND `batch_id`=`batch`.`batch_id`
      AND `center_id`=`centerId` GROUP BY `candidate_id`) AS `sfkjsd`
      WHERE `attendance_count` > 6
)
from `batch`