[DB]기초 쿼리문 예제

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;

UNION ALL과 함께 2023년 구매, 판매, 입금 및 결제 내역 보기
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;