在第三级子查询中获取父列

qf9go6mv 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(464)

如何从子查询中的子查询中最顶层的父查询中获取列？我必须像传递变量一样传递它吗？这是我的密码：

SELECT c.in_customer_id, 
( 
     SELECT
         group_concat(the_schedule separator '\r\n') 
     FROM
     ( 
         SELECT
             concat_ws('\n', 'Route: ', s.route_id, 'Interval: ', s.service_interval, 'Week No.: ', s.week_no, 'Weekdays: ', s.weekdays, 'Start Date: ', s.start_date, 'End Date: ', s.end_date, 'Start Time: ', s.start_time, 'End Time: ', s.end_time, '\n') AS the_schedule
         FROM
             schedule s 
         WHERE
             s.service_address_id IN 
             ( 
                 SELECT in_customer_address_id 
                   FROM   tbl_customer_address a2 
                  WHERE  a2.in_customer_id = c.in_customer_id
             ) 
             AND s.is_skipped = '0'
         GROUP BY
             s.service_address_id
     ) a
 )
     AS "Schedule"
 FROM
     tbl_customers c

我得到的回答是“错误代码：1054。“where子句”中的未知列“c.in\u customer\u id”

sql mysql subquery correlated-subquery mysql-error-1054

来源：https://stackoverflow.com/questions/53176148/get-parent-column-in-3rd-level-subquery

1条答案

按热度按时间

8fsztsew1#

原则上，您希望将子查询移到 FROM 条款。
试试这个。。。

SELECT
    c.in_customer_id,
    s.grouped_schedule
 FROM
    tbl_customers   AS c
 LEFT JOIN
 (
    SELECT
       in_customer_id,
       group_concat(the_schedule separator '\r\n') AS grouped_schedule
    FROM
    (
       SELECT
          a.in_customer_id,
          a.in_customer_address_id,
          concat_ws('\n', 'Route: ', s.route_id, 'Interval: ', s.service_interval, 'Week No.: ', s.week_no, 'Weekdays: ', s.weekdays, 'Start Date: ', s.start_date, 'End Date: ', s.end_date, 'Start Time: ', s.start_time, 'End Time: ', s.end_time, '\n') AS the_schedule
       FROM
          tbl_customer_address  AS a
       INNER JOIN
          schedule              AS s
             ON s.service_address_id = a.in_customer_address_id
       WHERE
          s.is_skipped = 0
    )
       AS schedules
    GROUP BY
       in_cusomer_id
)
   AS s
      ON s.in_customer_id = c.in_customer_id

赞(0）回复(0）举报 2021-06-18

我来回答

在第三级子查询中获取父列

1条答案

相关问题

热门标签

最新问答