MySQL关联子查询 [英] MySQL Correlated sub queries

问题描述

有人可以帮我解决相关嵌套子查询吗? 即，当我尝试在嵌套子查询中使用Grand Parent列时，出现此错误

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

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

Error Code: 1054 Unknown column 'scu.iUserId' in 'where clause'

    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 

推荐答案

两个级别的嵌套都会导致此错误.非常内部的子查询不知道"在外部查询中定义的scu.

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

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

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

这篇关于MySQL关联子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！