mysql in 关联子查询,MySQL关联子查询

Can any one help me i have problem with Correlated nested sub queries

i.e., when i am trying to use grand parent column in nested sub query then i got this error

Error Code: 1054

Unknown column 'scu.iUserId' in 'where clause'

Query:

SELECT

scu.iUserId,

(SELECT

SUM(

sbs.`iNoPointsBeginning` +

(SELECT

COALESCE(SUM(BehaviorPts), 0) AS StudentPts

FROM

(SELECT

(

COUNT(sbis.iIncidentSubmissionId) * sbi.iPointValue

) AS BehaviorPts

FROM

scn_behavior_incident_submission sbis

JOIN scn_behavior_incident_actors sbia

ON sbia.iIncidentSubmissionId = sbis.iIncidentSubmissionId

LEFT JOIN scn_behavior_incidents sbi

ON sbi.iIncidentId = sbis.iBehaviorIncidentId

WHERE sbia.iUserId = scu.iUserId

AND sbia.eActorType 'Witness'

AND sbis.iSchoolId = '875'

GROUP BY sbis.iBehaviorIncidentId) AS BehaviorTotal)

) AS stu_pt

FROM

scn_behavior_settings sbs

WHERE sbs.`iSchoolId` = '875')

FROM

scn_sections_members AS scm

INNER JOIN scn_users AS scu

ON scu.iUserId = scm.iStudentId

解决方案

The two levels of nesting cause this error. The very internal subquery does not "know" scu which is defined in the external query.

Try to rewrite it without inline subqueries. Not sure if this is the proper way but you'll get the idea. (the sbs table seems unrelated to the other ones so I made that a CROSS JOIN. Edit appropriately if there is a relationship):

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