mysql - 在 SQL 中使用嵌套连接表时出错

Question

我的查询是这样的

SELECT level1_idnt, day_dt, avg(sls_price) as avg_rp
FROM (SELECT * FROM (SELECT DISTINCT level1_idnt,day_dt,sls_price 
                     FROM (SELECT a.level1_idnt,a.day_dt,a.sls_price,b.op
                           FROM (SELECT level1_idnt,day_dt,sls_price 
                           FROM md1.loc_sku_dy_act_pos_full_v2 
                           WHERE seasn_cd =2 and day_dt >= '2015-03-01 00:00:00' and day_dt <= '2015-09-30 00:00:00'
                                )l0 
                           a left join (SELECT level1_idnt, AVG(org_sales_price) as op
                                        FROM (SELECT DISTINCT level1_idnt, org_sales_price
                                        FROM md1.item_lv1_org_price_m
                                        WHERE fr_cntry_cde = '01' AND org_sales_price IS NOT NULL
                                        ) l
                                        GROUP BY level1_idnt)l_op
                           b 
                           ON a.level1_idnt = b.level1_idnt
                           )l1
            WHERE sls_price <= op
      )l2
      WHERE sls_price is not null
     ) l3
GROUP BY level1_idnt,day_dt;

为此我得到一个错误

syntax error at or near "a"

如有任何帮助，我们将不胜感激

Answer 1

检查这个;) 它看起来像是子查询中的 alias 错误。

SELECT level1_idnt, day_dt, avg(sls_price) as avg_rp
FROM (SELECT * FROM (SELECT DISTINCT level1_idnt,day_dt,sls_price
                     FROM (SELECT a.level1_idnt,a.day_dt,a.sls_price,b.op
                           FROM (SELECT level1_idnt,day_dt,sls_price
                                 FROM md1.loc_sku_dy_act_pos_full_v2
                                 WHERE seasn_cd =2 and day_dt >= '2015-03-01 00:00:00' and day_dt <= '2015-09-30 00:00:00'
                                )
                          a left join (SELECT level1_idnt, AVG(org_sales_price) as op
                                       FROM (SELECT DISTINCT level1_idnt, org_sales_price
                                             FROM md1.item_lv1_org_price_m
                                             WHERE fr_cntry_cde = '01' AND org_sales_price IS NOT NULL
                                            ) l
                                       GROUP BY level1_idnt)
                    b
ON a.level1_idnt = b.level1_idnt
)l1
WHERE sls_price <= op
)l2
WHERE sls_price is not null
) l3
GROUP BY level1_idnt,day_dt;

在 a 之前有一个字符串 l0 在 b 之前有一个字符串 l_op