为什么case when中的sql语句从自己组合的a表中查就报错，从数据库存在的表中查就不报错？

[yigerendeshiguang]

报错结果：数据库中不存在a表
大家有什么比较好的解决方法吗？
select ( CASE WHEN loantype=2 then (select sum(debtfigure) from a where loantype=1) END ) as amount
from (SELECT
CASE
WHEN loan.financialbodyid = IFNULL(
repay.financialbodyid,
loan.financialbodyid
) THEN
loan.figure
ELSE
0
END AS debtfigure,
IFNULL(
CASE
WHEN loan.financialbodyid = IFNULL(
repay.financialbodyid,
loan.financialbodyid
) THEN
loan.figure
ELSE
0
END,
0
) - IFNULL(
SUM(repay.repaymentfigure),
0
) AS needfigure,
IFNULL(
SUM(repay.repaymentfigure),
0
) AS hvrepaymentfigure,
loan.loantype,
u.userid AS repaymentuserid,
u.username AS repaymentusername,
u.deptid AS repaymentdeptid,
u.deptname AS repaymentdeptname,
loan.financialbodyid AS financialbodyid,
unit.unitname AS financialbodyname
FROM
(
SELECT
SUM(figure) AS figure,
loantype,
userid,
financialbodyid
FROM
(
SELECT
SUM(debtfigure) AS figure,
loantype,
repaymentuserid AS userid,
financialbodyid
FROM
t_budget_hisrepaymentinfo t
GROUP BY
repaymentuserid,
financialbodyid,
loantype
UNION ALL
SELECT
SUM(paymentfigure),
loantype,
userid AS repaymentuserid,
companyid AS financialbodyid
FROM
t_budget_loaninfo
WHERE
(
paymentstatus = 1
OR iscashflow = 1
)
AND loanstatus = 3
GROUP BY
userid,
companyid,
loantype
) loantmp
GROUP BY
userid,
financialbodyid,
loantype
) loan
LEFT JOIN (
SELECT
repaymentuserid,
financialbodyid,
repaymenttype,
IFNULL(SUM(repaymentfigure), 0) AS repaymentfigure
FROM
t_budget_hisrepaymentinfo_tmp
GROUP BY
repaymentuserid,
financialbodyid,
repaymenttype
) repay ON repay.repaymentuserid = loan.userid
AND repay.financialbodyid = loan.financialbodyid
AND repay.repaymenttype = loan.loantype
LEFT JOIN t_sys_mnguserinfo u ON u.userid = loan.userid
LEFT JOIN t_sys_mngunitinfo unit ON loan.financialbodyid = unit.unitid
WHERE
1 = 1 and loan.userid =31724
GROUP BY loan.userid, loan.loantype, loan.financialbodyid, repay.financialbodyid) AS a

[shoppo0505]

这么长，根本没兴趣看。

[二月十六]

WITH    a AS ( SELECT   CASE WHEN loan.financialbodyid = IFNULL(repay.financialbodyid,
                                                              loan.financialbodyid)
                             THEN loan.figure
                             ELSE 0
                        END AS debtfigure ,
                        IFNULL(CASE WHEN loan.financialbodyid = IFNULL(repay.financialbodyid,
                                                              loan.financialbodyid)
                                    THEN loan.figure
                                    ELSE 0
                               END, 0) - IFNULL(SUM(repay.repaymentfigure), 0) AS needfigure ,
                        IFNULL(SUM(repay.repaymentfigure), 0) AS hvrepaymentfigure ,
                        loan.loantype ,
                        u.userid AS repaymentuserid ,
                        u.username AS repaymentusername ,
                        u.deptid AS repaymentdeptid ,
                        u.deptname AS repaymentdeptname ,
                        loan.financialbodyid AS financialbodyid ,
                        unit.unitname AS financialbodyname
               FROM     ( SELECT    SUM(figure) AS figure ,
                                    loantype ,
                                    userid ,
                                    financialbodyid
                          FROM      ( SELECT    SUM(debtfigure) AS figure ,
                                                loantype ,
                                                repaymentuserid AS userid ,
                                                financialbodyid
                                      FROM      t_budget_hisrepaymentinfo t
                                      GROUP BY  repaymentuserid ,
                                                financialbodyid ,
                                                loantype
                                      UNION ALL
                                      SELECT    SUM(paymentfigure) ,
                                                loantype ,
                                                userid AS repaymentuserid ,
                                                companyid AS financialbodyid
                                      FROM      t_budget_loaninfo
                                      WHERE     ( paymentstatus = 1
                                                  OR iscashflow = 1
                                                )
                                                AND loanstatus = 3
                                      GROUP BY  userid ,
                                                companyid ,
                                                loantype
                                    ) loantmp
                          GROUP BY  userid ,
                                    financialbodyid ,
                                    loantype
                        ) loan
                        LEFT JOIN ( SELECT  repaymentuserid ,
                                            financialbodyid ,
                                            repaymenttype ,
                                            IFNULL(SUM(repaymentfigure), 0) AS repaymentfigure
                                    FROM    t_budget_hisrepaymentinfo_tmp
                                    GROUP BY repaymentuserid ,
                                            financialbodyid ,
                                            repaymenttype
                                  ) repay ON repay.repaymentuserid = loan.userid
                                             AND repay.financialbodyid = loan.financialbodyid
                                             AND repay.repaymenttype = loan.loantype
                        LEFT JOIN t_sys_mnguserinfo u ON u.userid = loan.userid
                        LEFT JOIN t_sys_mngunitinfo unit ON loan.financialbodyid = unit.unitid
               WHERE    1 = 1
                        AND loan.userid = 31724
               GROUP BY loan.userid ,
                        loan.loantype ,
                        loan.financialbodyid ,
                        repay.financialbodyid
             )
    SELECT  ( CASE WHEN loantype = 2 THEN ( SELECT  SUM(debtfigure)
                                            FROM    a
                                            WHERE   loantype = 1
                                          )
              END ) AS amount
    FROM    a;

[吉普赛的歌]

这样试试：

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v6.5.278
 * Time: 2017/9/20 21:06:28
 ************************************************************/


;WITH cte AS 
(
   SELECT CASE 
               WHEN loan.financialbodyid = IFNULL(repay.financialbodyid, loan.financialbodyid) THEN 
                    loan.figure
               ELSE 0
          END                   AS debtfigure,
          IFNULL(
              CASE 
                   WHEN loan.financialbodyid = IFNULL(repay.financialbodyid, loan.financialbodyid) THEN 
                        loan.figure
                   ELSE 0
              END,
              0
          ) - IFNULL(SUM(repay.repaymentfigure), 0) AS needfigure,
          IFNULL(SUM(repay.repaymentfigure), 0) AS hvrepaymentfigure,
          loan.loantype,
          u.userid              AS repaymentuserid,
          u.username            AS repaymentusername,
          u.deptid              AS repaymentdeptid,
          u.deptname            AS repaymentdeptname,
          loan.financialbodyid  AS financialbodyid,
          unit.unitname         AS financialbodyname
   FROM   (
              SELECT SUM(figure)  AS figure,
                     loantype,
                     userid,
                     financialbodyid
              FROM   (
                         SELECT SUM(debtfigure) AS figure,
                                loantype,
                                repaymentuserid AS userid,
                                financialbodyid
                         FROM   t_budget_hisrepaymentinfo t
                         GROUP BY
                                repaymentuserid,
                                financialbodyid,
                                loantype
                         UNION ALL
                         SELECT SUM(paymentfigure),
                                loantype,
                                userid AS repaymentuserid,
                                companyid AS financialbodyid
                         FROM   t_budget_loaninfo
                         WHERE  (paymentstatus = 1 OR iscashflow = 1)
                                AND loanstatus = 3
                         GROUP BY
                                userid,
                                companyid,
                                loantype
                     )               loantmp
              GROUP BY
                     userid,
                     financialbodyid,
                     loantype
          ) loan
          LEFT JOIN (
                   SELECT repaymentuserid,
                          financialbodyid,
                          repaymenttype,
                          IFNULL(SUM(repaymentfigure), 0) AS 
                          repaymentfigure
                   FROM   t_budget_hisrepaymentinfo_tmp
                   GROUP BY
                          repaymentuserid,
                          financialbodyid,
                          repaymenttype
               ) repay
               ON  repay.repaymentuserid = loan.userid
               AND repay.financialbodyid = loan.financialbodyid
               AND repay.repaymenttype = loan.loantype
          LEFT JOIN t_sys_mnguserinfo u
               ON  u.userid = loan.userid
          LEFT JOIN t_sys_mngunitinfo unit
               ON  loan.financialbodyid = unit.unitid
   WHERE  1 = 1
          AND loan.userid = 31724
   GROUP BY
          loan.userid,
          loan.loantype,
          loan.financialbodyid,
          repay.financialbodyid
)
SELECT (
           CASE WHEN loantype = 2 THEN (
                         SELECT SUM(debtfigure)
                         FROM  cte AS a
                         WHERE  loantype = 1
                     )
           END
       )  AS amount
FROM   cte

[yigerendeshiguang]

为啥mysql不认识with [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a AS ( SELECT CASE WHEN loan.financialbodyid = IFNULL(repay.financialbodyid, ' at line 1