MySQL关联的子查询

Question

有人能帮我处理相关嵌套子查询吗也就是说，当我试图在嵌套子查询中使用grand-parent列时，我得到了这个错误

错误代码：1054"where子句"中的未知列"scu.iUserId"

查询：

选择scu.iUserId，（选择总和(sbs`iNoPoints开始`+（选择COALESCE（SUM（BehaviorPts），0）作为StudentPts来自（选择(COUNT（sbis.iIncidentSubmissionId）*sbi.iPointValue)AS行为准则来自scn_behavior_cident_submission sbis加入scn_behavior_cident_actors sbiaON sbia.iIncidentSubmissionId=sbis.iIncidentSubmissionIdLEFT JOIN scn_behavior_cidents sbiON sbi.IncidentId=sbis.iBehaviorIncidentId其中sbia.iUserId=scu.iUserIdAND sbia.eActorType"见证"AND sbis.iSchoolId='875'按sbis.iBehaviorIncidentId分组）作为BehaviorTotal）)AS stu_pt来自scn_behavior设置sbs其中sbs`iSchoolId `='875'）来自scn_sections_members AS scmINNER将scn_users作为scu加入打开scu.iUserId=scm.iStudentId

Answer 1

两个级别的嵌套导致了此错误。非常内部的子查询不"知道"在外部查询中定义的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

Answer 2

如果你（暂时）去除了混乱的子查询，你会看到你在说：

SELECT scu.iUserId 
FROM scn_sections_members AS scm 
INNER JOIN scn_users AS scu 
ON scu.iUserId = scm.iStudentId

MySQL告诉您该列是未知的，这一定意味着该列不存在。scn_users的定义是什么？在该表中放入一个iUserId列，那么至少问题的这一部分就会结束。