[DB] DB sql문 2

거래 잔액 = 기초잔액 + 매출 -매입 - 입금 + 지급

 

-- Q1.  tmoney(거래 잔액) 를 구하는 sql문을 적어보자

update zcompany
SET tmoney=smoney + IFNULL((SELECT sum(zpan.total) FROM zpan 
		 		WHERE zpan.cid=zcompany.id and zpan.type='매출'),0)
                  - IFNULL((SELECT sum(zpan.total) FROM zpan 
                  	         WHERE zpan.cid=zcompany.id and zpan.type='매입'),0)
- IFNULL((SELECT sum(zmoney.money) FROM zmoney WHERE zmoney.cid=zcompany.id and zmoney.type='입금'),0)
+ IFNULL((SELECT sum(zmoney.money) FROM zmoney WHERE zmoney.cid=zcompany.id and zmoney.type='지급'),0);

 

 

 

-- Q2. 거래처, 매출액, 매입액, 입금액, 지급액, 잔액(tmoney), tmoney = ( smoney - )

 

SELECT zcom.cname, SUM(t1), SUM(t2), SUM(t3), SUM(t4), zcom.tmoney, smoney+sum(t1)-sum(t2)-sum(t3)+sum(t4)
FROM (

SELECT cid as s1, SUM(total) as t1, 0 as t2, 0 as t3, 0 as t4 FROM zpan WHERE type='매출'
GROUP BY s1

UNION ALL
SELECT cid as s1, 0 as t1, SUM(total) as t2, 0 as t3, 0 as t4 FROM zpan WHERE type='매입'
GROUP BY s1

UNION ALL
SELECT cid as s1, 0 as t1, 0 as t2, SUM(money) as t3, 0 as t4 FROM zmoney WHERE type='입금'
GROUP BY s1

UNION ALL
SELECT cid as s1, 0 as t1, 0 as t2, 0 as t3, SUM(money) as t4 FROM zmoney WHERE type='지급'
GROUP BY s1
) as k LEFT JOIN zcompany as zcom on k.s1 = zcom.id

GROUP BY s1;

 

sql문 결과값

 

 

 

-- Q2-1. 위의 sql문에 if문을 써서 쿼리문 줄이기

 

SELECT zcom.cname, SUM(t1), SUM(t2), SUM(t3), SUM(t4), zcom.tmoney, smoney+sum(t1)-sum(t2)-sum(t3)+sum(t4)
FROM (

SELECT cid as s1, SUM(IF(type='매출', total, 0)) as t1, SUM(IF(type='매입', total, 0)) as t2, 0 as t3, 0 as t4 FROM zpan
GROUP BY s1


UNION ALL
SELECT cid as s1, 0 as t1, 0 as t2, SUM(IF(type='입금', money, 0)) as t3, SUM(IF(type='지급', money, 0)) as t4 FROM zmoney
GROUP BY s1


) as k LEFT JOIN zcompany as zcom on k.s1 = zcom.id

GROUP BY s1;

 

sql문 결과값

 

--Q2-2. 보다 완벽한 sql문

 

SELECT zcom.cname, SUM(t1), SUM(t2), sum(t3), SUM(t4), zcom.tmoney, smoney+sum(t1)-sum(t2)-sum(t3)+sum(t4) as t5
FROM (

	SELECT cid as s1, SUM(total) as t1, 0 as t2, 0 as t3, 0 as t4 FROM zpan WHERE type='매출'
	GROUP BY s1

	UNION ALL
	SELECT cid as s1, 0 as t1, SUM(total) as t2, 0 as t3, 0 as t4 FROM zpan WHERE type='매입'
	GROUP BY s1

	UNION ALL
	SELECT cid as s1, 0 as t1, 0 as t2, SUM(money) as t3, 0 as t4 FROM zmoney WHERE type='입금'
	GROUP BY s1

	UNION ALL
	SELECT cid as s1, 0 as t1, 0 as t2, 0 as t3, SUM(money) as t4 FROM zmoney WHERE type='지급'
	GROUP BY s1

	UNION ALL
	SELECT id as s1, 0 as t1, 0 as t2, 0 as t3, 0 as t4
	FROM zcompany
	
	

) as k LEFT JOIN zcompany as zcom on k.s1 = zcom.id
GROUP BY s1
HAVING t5<>0

ORDER BY zcom.cname;