查询客户余额

oracle

SELECT
	SUM (
		CASE
		WHEN C. TYPE = 'DEDUTION' THEN
			C.BALANCE
		ELSE
			0
		END
	) AS DEDUTION,
	SUM (
		CASE
		WHEN C. TYPE = 'PAY' THEN
			C.BALANCE
		ELSE
			0
		END
	) AS PAY,
	C.DEPT_NAME AS DEPT_NAME,
	C.DEPT_CODE AS DEPT_CODE
FROM
	(
		SELECT
			NVL (SUM(F.PAPER_AMT), 0) AS BALANCE,
			F.DEPT_NAME AS DEPT_NAME,
			F.DEPT_CODE AS DEPT_CODE,
			(
				CASE F.NATURE
				WHEN '10' THEN
					-- 扣款
					'DEDUTION'
				WHEN '50' THEN
					-- 付款
					'PAY'
				END
			) AS TYPE
		FROM
			FM_CAPITAL_FLOW_SUPP F
		WHERE
			1 = 1
		AND F.status = '0'
		GROUP BY
			F.nature,
			F.DEPT_NAME,
			F.DEPT_CODE
	) C
GROUP BY
	C.DEPT_NAME,
	C.DEPT_CODE