mysql相关子查询+in_MySQL相关子查询

嵌套的两个级别会导致此错误。内部子查询不“知道”外部查询中定义的scu。

尝试在没有内联子查询的情况下重写它。不确定这是否是正确的方法，但你会明白。 (sbs表似乎与其他表无关，因此我将其设为CROSS JOIN。如果存在关系则适当编辑)：

SELECT

scu.iUserId,

sbs.iNoPointsBeginning

+ COUNT(sbis.iIncidentSubmissionId) * COALESCE(sbi.iPointValue, 0)

AS stu_pt

FROM

( SELECT

SUM(sbs.iNoPointsBeginning) AS iNoPointsBeginning

FROM

scn_behavior_settings sbs

WHERE sbs.iSchoolId = '97'

) AS sbs

CROSS JOIN

scn_sections_members AS scm

INNER JOIN scn_users AS scu

ON scu.iUserId = scm.iStudentId

LEFT JOIN

scn_behavior_incident_submission sbis

JOIN scn_behavior_incident_actors sbia

ON sbia.iIncidentSubmissionId = sbis.iIncidentSubmissionId

AND sbia.eActorType = 'Witness'

AND sbis.iSchoolId = '97'

LEFT JOIN scn_behavior_incidents sbi

ON sbi.iIncidentId = sbis.iBehaviorIncidentId

ON sbia.iUserId = scu.iUserId

GROUP BY scu.iUserId

, sbis.iBehaviorIncidentId