22,209
社区成员
发帖
与我相关
我的任务
分享
/************************************************************
* 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
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;