[DB]기초 쿼리문 예제

 

zcompany.sql

0.01MB

zmoney.sql

0.00MB

zpan.sql

0.00MB

zproduct.sql

0.00MB

2023년 매입매출을 보여주세요
   1. 날짜, 구분, 거래처명, 제품명, 제품규격, 수량, 단가 합계
   2. 날짜, 구분, 거래처명, (제품명+제품규격), 수량, 단가 합계

 

SELECT  a.indate, a.type, b.cname, c.pname, c.psize, a.cnt, a.price, a.total  FROM zpan as a LEFT JOIN zcompany as b on a.cid = b.id LEFT JOIN zproduct as c on c.id = a.pid 
WHERE SUBSTRING(a.indate,1,4) = '2023';

SELECT a.indate, a.type, b.cname, CONCAT(c.pname,' ', c.psize), a.cnt, a.price, a.total FROM zpan as a LEFT JOIN zcompany as b on a.cid = b.id LEFT JOIN zproduct as c on c.id = a.pid 
WHERE a.indate BETWEEN '2023-01-01' AND '2023-12-31';

 

 

2023년 거래처별, 구분별 합계를 보여주세요(매출,매입)
   1. 거래처명, 구분, 합계

2023년 거래처별, 구분별 금액합계을 보여주세요(입금,지급)
   2. 거래처명, 구분, 금액합계

 

 

SELECT b.cname, a.type, SUM(a.total)  FROM zpan as a LEFT JOIN zcompany as b on a.cid = b.id WHERE SUBSTRING(a.indate,1,4)= '2022' GROUP BY a.cid, a.type ORDER BY b.cname, a.type;

SELECT b.cname, a.type, SUM(a.money) FROM zmoney as a LEFT JOIN zcompany as b on a.cid = b.id WHERE SUBSTRING(a.indate,1,4)= '2023' GROUP BY a.cid, a.type ORDER BY b.cname, a.type;

 

 

 

 

 

 

2023년 매입,매출,입금,지급을 UNION ALL 을 사용하여 보여주세요
   1. 날짜, 거래처명, 구분, (제품명+제품규격) 또는 결제방법, 수량, 단가, 합계, 금액 
     정렬 : 날짜, 생성일

 

 

SELECT a.indate as sort1, b.cname, a.type, CONCAT(c.pname,' ', c.psize), a.cnt, a.price, a.total, a.dtcreate as sort2 FROM zpan as a LEFT JOIN zcompany as b on a.cid = b.id LEFT JOIN zproduct as c on c.id = a.pid WHERE a.indate BETWEEN '2023-01-01' AND '2023-12-31'
UNION ALL
SELECT a.indate as sort1, b.cname, a.type, a.method, '', '', a.money, a.dtcreate as sort2 FROM zmoney as a LEFT JOIN zcompany as b on a.cid = b.id  WHERE a.indate BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY sort1, sort2;

 

 

 

GROUP BY 사용해보기

 

SELECT b.cname, a.type, SUM(a.total), 0, 0, 0 FROM zpan as a LEFT JOIN zcompany as b on a.cid=b.id WHERE type='매출' GROUP BY a.cid
UNION ALL
SELECT b.cname, a.type, 0, SUM(a.total), 0, 0 FROM zpan as a LEFT JOIN zcompany as b on a.cid=b.id WHERE type='매입' GROUP BY a.cid
UNION ALL
SELECT b.cname, a.type, 0, 0, SUM(a.money), 0  FROM zmoney as a LEFT JOIN zcompany as b on a.cid=b.id WHERE type='입금' GROUP BY a.cid
UNION ALL
SELECT b.cname, a.type, 0, 0, 0, SUM(a.money) FROM zmoney as a LEFT JOIN zcompany as b on a.cid=b.id WHERE type='지급' GROUP BY a.cid
ORDER BY cname, type;

 

IF문으로 만들어보기

 

SELECT b.cname, a.type, SUM(IF(a.type='매출', a.total, 0)), SUM(IF(a.type='매입', a.total, 0)), 0, 0 
FROM zpan as a LEFT JOIN zcompany as b on a.cid=b.id 
GROUP BY a.cid

UNION ALL
SELECT b.cname, a.type, 0, 0, SUM(IF(a.type='입금', a.money, 0)), SUM(IF(a.type='지급', a.money, 0)) 
FROM zmoney as a LEFT JOIN zcompany as b on a.cid=b.id
GROUP BY a.cid

ORDER BY cname;

 

1. 2023년 매출은?

SELECT SUM(total) from zpan WHERE SUBSTRING(indate,1,4) = '2023' and type = '매출';

 

거래처명 매출 매입 지금 입금 잔액을 한줄로 표시

 

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

 

SELECT a.cname, SUM(t1), SUM(t2), SUM(t3), SUM(t4),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 cid

	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 cid
) as k 
LEFT JOIN zcompany as a on k.s1=a.id
GROUP BY s1;