access转sql server为什么总是提示(列前缀 "SP " 与查询中所用的表名或别名不匹配

Question

下面这段代码在企业管理器中总出出错误提示:列前缀   "SP "   与查询中所用的表名或别名不匹配,请大家帮忙看下问题出在哪里,谢谢了!
SELECT rkxsjc.*, jcsl + rksl - xssl AS bqsl, jcje + rkje - xsje AS bqje, SP.SPMC, 
      SP.spbm
FROM (SELECT rkxs.*, isnull(jcT.sl, 0) AS jcsl, isnull(jcT.je, 0) AS jcje
        FROM (SELECT rk.*, isnull(xs.xssl, 0) AS xssl, isnull(xs.xsje, 0) AS xsje
                FROM (SELECT sp.spid, SUM(rkdm.rksl) AS rksl, SUM(rkdm.rkje) 
                              AS rkje
                        FROM (SELECT sp.spid, isnull(rkdmx.sl, 0) AS rksl, isnull(rkdmx.je, 0) 
                                      AS rkje
                                FROM sp LEFT JOIN
                                          (SELECT rkd_mx.spid, rkd_mx.sl, 
                                               (rkd_mx.sl * rkd_mx.cbdj) AS je
                                         FROM (SELECT *
                                                 FROM rkd_zb
                                                 WHERE rkrq >= '2009-7-01' AND 
                                                       rkrq <= '2009/07/31') AS rkd LEFT JOIN
                                               rkd_mx ON rkd.rkdbh = rkd_mx.rkdbh) 
                                      AS rkdmx ON sp.spid = rkdmx.spid) rkdm
                        GROUP BY sp.spid) AS rk LEFT JOIN
                          (SELECT xsd_mx.spid, SUM(xsd_mx.sl) AS xssl, 
                               SUM(xsd_mx.cbdj * xsd_mx.sl) AS xsje
                         FROM (SELECT *
                                 FROM xsd_zb
                                 WHERE xsrq >= '2009-7-01' AND xsrq <= '2009/07/31') 
                               AS xsd LEFT JOIN
                               xsd_mx ON xsd.xsdbh = xsd_mx.xsdbh
                         GROUP BY xsd_mx.spid) AS xs ON rk.spid = xs.spid) AS rkxs LEFT 
              JOIN
                  (SELECT spid, jcsl AS sl, jcje AS je
                 FROM jcsp
                 WHERE jcny = '2009/07/01') AS jcT ON rkxs.spid = jcT.spid) 
      rkxsjc INNER JOIN
      SP ON rkxsjc.spid = SP.SPID

Answer 1

这是因为你的查询里用到了2个SP，会有冲突的，建议给最后这个SP换个别名：以避免和子查询里面的冲突。
前面相关的也要修改。
SP sp1 ON rkxsjc.spid = SP1.SPID

Answer 2

....
(SELECT sp.spid, SUM(rkdm.rksl) AS rksl, SUM(rkdm.rkje) 
                              AS rkje 
                        FROM (SELECT sp.spid, isnull(rkdmx.sl, 0) AS rksl, isnull(rkdmx.je, 0) 
                                      AS rkje 
                                FROM sp LEFT JOIN 
                                          (SELECT rkd_mx.spid, rkd_mx.sl, 
                                              (rkd_mx.sl * rkd_mx.cbdj) AS je 
                                        FROM (SELECT * 
                                                FROM rkd_zb 
                                                WHERE rkrq >= '2009-7-01' AND 
                                                      rkrq <= '2009/07/31') AS rkd LEFT JOIN 
                                              rkd_mx ON rkd.rkdbh = rkd_mx.rkdbh) 
                                      AS rkdmx ON sp.spid = rkdmx.spid) rkdm 
                        GROUP BY sp.spid) AS rk
...====>...
....
(SELECT spid, SUM(rkdm.rksl) AS rksl, SUM(rkdm.rkje) 
                              AS rkje 
                        FROM (SELECT sp.spid, isnull(rkdmx.sl, 0) AS rksl, isnull(rkdmx.je, 0) 
                                      AS rkje 
                                FROM sp LEFT JOIN 
                                          (SELECT rkd_mx.spid, rkd_mx.sl, 
                                              (rkd_mx.sl * rkd_mx.cbdj) AS je 
                                        FROM (SELECT * 
                                                FROM rkd_zb 
                                                WHERE rkrq >= '2009-7-01' AND 
                                                      rkrq <= '2009/07/31') AS rkd LEFT JOIN 
                                              rkd_mx ON rkd.rkdbh = rkd_mx.rkdbh) 
                                      AS rkdmx ON sp.spid = rkdmx.spid) rkdm 
                        GROUP BY spid) AS rk
...
...

Answer 3

拆分开一步步找原因:
SELECT * 
                                                FROM rkd_zb 
                                                WHERE rkrq >= '2009-7-01' AND 
                                                      rkrq <= '2009/07/31'
SELECT rkd_mx.spid, rkd_mx.sl, 
                                              (rkd_mx.sl * rkd_mx.cbdj) AS je 
                                        FROM (SELECT * 
                                                FROM rkd_zb 
                                                WHERE rkrq >= '2009-7-01' AND 
                                                      rkrq <= '2009/07/31') AS rkd LEFT JOIN 
                                              rkd_mx ON rkd.rkdbh = rkd_mx.rkdbh
SELECT sp.spid, isnull(rkdmx.sl, 0) AS rksl, isnull(rkdmx.je, 0) 
                                      AS rkje 
                                FROM sp LEFT JOIN 
                                          (SELECT rkd_mx.spid, rkd_mx.sl, 
                                              (rkd_mx.sl * rkd_mx.cbdj) AS je 
                                        FROM (SELECT * 
                                                FROM rkd_zb 
                                                WHERE rkrq >= '2009-7-01' AND 
                                                      rkrq <= '2009/07/31') AS rkd LEFT JOIN 
                                              rkd_mx ON rkd.rkdbh = rkd_mx.rkdbh) 
                                      AS rkdmx ON sp.spid = rkdmx.spid
......
没见过如此的牛人,这样一层层嵌套下去,够累吧,效率恐怕也会很低.

Answer 4

第五行
  FROM (SELECT spid, SUM(rkdm.rksl) AS rksl, SUM(rkdm.rkje) 

Answer 5

SELECT rkxsjc.*, jcsl + rksl - xssl AS bqsl, jcje + rkje - xsje AS bqje, SP.SPMC, SP.spbm 
FROM (
SELECT rk.*, isnull(xs.xssl, 0) AS xssl, isnull(xs.xsje, 0) AS xsje 
isnull(jcsp.sl, 0) AS jcsl, isnull(jcsp.je, 0) AS jcje
                FROM (
SELECT sp.spid, sum(isnull(rkdmx.sl, 0)) AS rksl, sum(isnull(rkdmx.je, 0))  AS rkje 
                  FROM sp LEFT JOIN (
                              SELECT rkd_mx.spid, rkd_mx.sl, 
                                      (rkd_mx.sl * rkd_mx.cbdj) AS je 
                          FROM rkd_zb LEFT JOIN rkd_mx 
                                    ON rkd_zb.rkdbh = rkd_mx.rkdbh 
                              WHERE rkd_zb.rkrq >= '2009-7-01' AND 
                                      rkd_zb.rkrq <= '2009/07/31'
                          )AS rkdmx ON sp.spid = rkdmx.spid 
                         GROUP BY sp.spid
) AS rk LEFT JOIN (
SELECT xsd_mx.spid, SUM(xsd_mx.sl) AS xssl, 
                              SUM(xsd_mx.cbdj * xsd_mx.sl) AS xsje 
                         FROM (
SELECT xsdbh
                                FROM xsd_zb 
                                WHERE xsrq >= '2009-7-01' AND xsrq <= '2009/07/31'
) AS xsd LEFT JOIN  xsd_mx ON xsd.xsdbh = xsd_mx.xsdbh 
                         GROUP BY xsd_mx.spid
) AS xs ON rk.spid = xs.spid
LEFT JOIN jcsp ON rk.spid = jcsp.spid
               WHERE jcsp.jcny = '2009/07/01'
)AS rkxsjc INNER JOIN  SP ON rkxsjc.spid = SP.SPID

Answer 6

如果还用楼主的那种写法的话,应为:
SELECT rkxsjc.*, jcsl + rksl - xssl AS bqsl, jcje + rkje - xsje AS bqje, SP.SPMC, 
      SP.spbm 
FROM (SELECT rkxs.*, isnull(jcT.sl, 0) AS jcsl, isnull(jcT.je, 0) AS jcje 
        FROM (SELECT rk.*, isnull(xs.xssl, 0) AS xssl, isnull(xs.xsje, 0) AS xsje 
                FROM (SELECT rkdm.spid, SUM(rkdm.rksl) AS rksl, SUM(rkdm.rkje) 
                              AS rkje 
                        FROM (SELECT sp.spid, isnull(rkdmx.sl, 0) AS rksl, isnull(rkdmx.je, 0) 
                                      AS rkje 
                                FROM sp LEFT JOIN 
                                          (SELECT rkd_mx.spid, rkd_mx.sl, 
                                              (rkd_mx.sl * rkd_mx.cbdj) AS je 
                                        FROM (SELECT * 
                                                FROM rkd_zb 
                                                WHERE rkrq >= '2009-7-01' AND 
                                                      rkrq <= '2009/07/31') AS rkd LEFT JOIN 
                                              rkd_mx ON rkd.rkdbh = rkd_mx.rkdbh) 
                                      AS rkdmx ON sp.spid = rkdmx.spid) rkdm 
                        GROUP BY rkdm.spid) AS rk LEFT JOIN 
                          (SELECT xsd_mx.spid, SUM(xsd_mx.sl) AS xssl, 
                              SUM(xsd_mx.cbdj * xsd_mx.sl) AS xsje 
                        FROM (SELECT * 
                                FROM xsd_zb 
                                WHERE xsrq >= '2009-7-01' AND xsrq <= '2009/07/31') 
                              AS xsd LEFT JOIN 
                              xsd_mx ON xsd.xsdbh = xsd_mx.xsdbh 
                        GROUP BY xsd_mx.spid) AS xs ON rk.spid = xs.spid) AS rkxs LEFT 
              JOIN 
                  (SELECT spid, jcsl AS sl, jcje AS je 
                FROM jcsp 
                WHERE jcny = '2009/07/01') AS jcT ON rkxs.spid = jcT.spid) 
      rkxsjc INNER JOIN 
      SP ON rkxsjc.spid = SP.SPID