데이터베이스 질문과 답변

구매수량 쿼리 요청 0 7 2,335

by 초보oracle [SQL Query] 테이블스페이스 [2016.03.18 11:56:18]

안녕하세요 도저희 어떻게 쿼리를 짜야할지 몰라서 도움 요청드립니다.

예를 들어

 

bo라는 테이블에 2개의 컬럼이 있습니다.

bonumber boname
5989 포도
9365 포도
10327 바나나
4384 바나나

po라는 테이블에 3개의 컬럼이 있습니다.

poseq ponumber poname
1 5989 포도즙
2 9365 포도주스
3 10327 바나나주스
4 4384 바나나우유

xo라는 테이블에는 2개 컬럼이 있습니다.

poseq volume
1 10
2 3
3 5
4 null

 

bo 테이블과 po 테이블과 xo 테이블을 조인하여

select distinct a.boname as 이름, sum(case when c.volume is null then 0 else c.volume end) as 구매수량 from bo a

left outer join po b on a.bonumber=b.ponumber

left outer join xo c on b.poseq=x.poseq

groupby boname

로하여 현재 결과가

이름 구매수량
포도 13
바나나 5

위와 같이 결과가 나왔습니다.

jo 테이블에는 2개의 컬럼이 있습니다. user 100이라는사람이 poseq 1인포도즙을 구매한것입니다.

poseq user
1 100
1 200
2 300
2 400
3 500
4 600

여기서 이제 제가 원하고자 하는 결과는

이름 구매수량 판매수량
포도 13 4
바나나 5 2

위와같은 결과가 나오는 판매수량입니다.

그래서 마지막으로 최종 결과는 

이름 구매수량 판매수량 잔여수량
포도 13 4 9
바나나 5 2 3

도저희 쿼리를 짜보려고해도 어떻게 해야할지 몰라 문의 드립니다.

힌트라도 주시면 감사하겠습니다.

by 창조의날개 [2016.03.18 13:27:30] 

WITH BO(BONUMBER,	BONAME) AS (
      SELECT 5989,	'포도' FROM DUAL UNION ALL
      SELECT 9365,	'포도' FROM DUAL UNION ALL
      SELECT 10327,	'바나나' FROM DUAL UNION ALL
      SELECT 4384,	'바나나' FROM DUAL
)
, PO (POSEQ,	PONUMBER,	PONAME) AS (
      SELECT 1,	5989,	  '포도즙' FROM DUAL UNION ALL
      SELECT 2,	9365,	  '포도주스' FROM DUAL UNION ALL
      SELECT 3,	10327,	'바나나주스' FROM DUAL UNION ALL
      SELECT 4,	4384,	  '바나나우유' FROM DUAL
)
, XO(POSEQ,	VOLUME) AS (
      SELECT 1,	10 FROM DUAL UNION ALL
      SELECT 2,	3 FROM DUAL UNION ALL
      SELECT 3,	5 FROM DUAL UNION ALL
      SELECT 4,	NULL FROM DUAL
)
, JO(POSEQ,	"user") AS(
      SELECT 1,	100 FROM DUAL UNION ALL
      SELECT 1,	200 FROM DUAL UNION ALL
      SELECT 2,	300 FROM DUAL UNION ALL
      SELECT 2,	400 FROM DUAL UNION ALL
      SELECT 3,	500 FROM DUAL UNION ALL
      SELECT 4,	600 FROM DUAL
)
SELECT AA.이름, BB.구매수량, AA.판매수량, BB.구매수량-AA.판매수량 잔여수량
FROM (
      SELECT A.BONAME AS 이름
           , COUNT(*) AS  판매수량
      FROM BO A
         , PO B
         , JO C
      WHERE A.BONUMBER = B.PONUMBER
        AND B.POSEQ = C.POSEQ
      GROUP BY A.BONAME
      ) AA
      ,(
      SELECT A.BONAME AS 이름
           , SUM(C.VOLUME) AS 구매수량 
      FROM BO A
         , PO B
         , XO C
      WHERE A.BONUMBER = B.PONUMBER
        AND B.POSEQ = C.POSEQ
      GROUP BY A.BONAME
      ) BB
WHERE AA.이름 = BB.이름
;

 

by 초보oracle [2016.03.18 14:04:24]

답변 정말 감사합니다.

제가 위의 설명을 좀더 자세히 했어야 했는데 죄송합니다.

제가 한가지 설명을 빼먹은게 있어서 댓글드립니다.

1. bo 테이블에 값이 총 271개가 있습니다. 양이많아 4개만 예를 들어쓴겁니다.

2. po 테이블에 값은 총 809개가 있습니다. 포도즙,포도주스 = 포도 로 한거와 같이 809개가 나열되어있습니다. 그걸 간추려 271개가 된거구여

2. xo 테이블에는 volume이 0인 것도 있습니다.

3.  jo 테이블에는 po테이블에있는 seq를 가지고 있는것도 있고 안가지고 있는것도 있습니다.

최종 결과를 추가 예를 들자면 

 

이름 구매수량 판매수량 잔여수량
포도 13 4 9
바나나 5 2 3
딸기 0 3 -3
오렌지 0 0 0

결론은 271개 이름에 대해서 전부 나와야 합니다.

한번더 도움 주시면 감사하겠습니다.

by 마농 [2016.03.18 15:03:55] 
SELECT bo.boname 이름
     , NVL(SUM(xo.volume), 0) 구매수량
     , NVL(SUM(jo.cnt   ), 0) 판매수량
     , NVL(SUM(xo.volume), 0) - NVL(SUM(jo.cnt), 0) 잔여수량
  FROM bo
  LEFT OUTER JOIN po
    ON bo.bonumber = po.ponumber
  LEFT OUTER JOIN xo
    ON po.poseq = xo.poseq
  LEFT OUTER JOIN
       (SELECT poseq
             , COUNT(*) cnt
          FROM jo
         GROUP BY poseq
        ) jo
    ON po.poseq = jo.poseq
 GROUP BY bo.boname
;

 

by 초보oracle [2016.03.18 16:06:19]

와 정확히 나오네요 마농님 정말 감사합니다.

 

left outer join 에 안에 Select 문을 넣어 조인생각은 했었는데 어떻게 짜야할지 몰랐는데

정말 많은걸 배워 갑니다. 감사합니다.!!

by 초보oracle [2016.03.18 16:56:31]

마농님 제가 한가지 빼먹은게 있었는데 이거는 제가하려고 했는데 값이 자꾸 이상하게 나오네여ㅠㅠ

1. 구매한 사람들을 종합할때 예외 해야할 사람들이 있어서 그사람들은 예외하고 합계를해야해서

근데 문제는 jo 테이블에 있는 user와

users라는 테이블(예로 데이터2개,실제 데이터 양은 많음)에

user userseq
100 52662
200 71334

employee라는 테이블에

userseq userid orgseq
52662 693156 13823
71334 x151100024 12903

orgs라는 테이블에

orgseq orgcode
13823 p001
12903 xp001

이렇게 되어있어서

쿼리 주신거에 users라는 테이블과 user테이블과 조인하였고

users라는 테이블과 employee 테이블과 조인하였고

employee 테이블과 orgs 테이블과 조인하여

마지막 where 절로

orgs.orgcode not like 'x%' and employees.employeeid not like 'x%'를하였으나

ora-00904로 user,user :부적합한 식별자라는 에러가 나오더라구여

그래서 보니깐 마지막 조인 부분에 user 조인한거 보니깐

user.poseq만 가져오더라구여 그것때문에 그런거 같은데

orgcode와 employeeid에 x로 들어가는 사람은 제외하고 수량 확인하고싶은데 가능할까요??

 

by 마농 [2016.03.21 08:21:46] 
WITH bo(bonumber, boname) AS
(
          SELECT  5989, '포도'   FROM dual
UNION ALL SELECT  9365, '포도'   FROM dual
UNION ALL SELECT 10327, '바나나' FROM dual
UNION ALL SELECT  4384, '바나나' FROM dual
)
, po(poseq, ponumber, poname) AS
(
          SELECT 1,  5989, '포도즙'     FROM dual
UNION ALL SELECT 2,  9365, '포도주스'   FROM dual
UNION ALL SELECT 3, 10327, '바나나주스' FROM dual
UNION ALL SELECT 4,  4384, '바나나우유' FROM dual
)
, xo(poseq, volume) AS
(
          SELECT 1,   10 FROM dual
UNION ALL SELECT 2,    3 FROM dual
UNION ALL SELECT 3,    5 FROM dual
UNION ALL SELECT 4, null FROM dual
)
, jo(poseq, usr) AS
(
          SELECT 1, 100 FROM dual
UNION ALL SELECT 1, 200 FROM dual
UNION ALL SELECT 2, 300 FROM dual
UNION ALL SELECT 2, 400 FROM dual
UNION ALL SELECT 3, 500 FROM dual
UNION ALL SELECT 4, 600 FROM dual
)
, users(usr, userseq) AS
(
          SELECT 100, 52662 FROM dual
UNION ALL SELECT 200, 71334 FROM dual
)
, employee(userseq, userid, orgseq) AS
(
          SELECT 52662, '693156'    , 13823 FROM dual
UNION ALL SELECT 71334, 'x151100024', 12903 FROM dual
)
, orgs(orgseq, orgcode) AS
(
          SELECT 13823, 'p001'  FROM dual
UNION ALL SELECT 12903, 'xp001' FROM dual
)
SELECT bo.boname 이름
     , NVL(SUM(xo.volume), 0) 구매수량
     , NVL(SUM(jo.cnt), 0) 판매수량
     , NVL(SUM(xo.volume), 0) - NVL(SUM(jo.cnt), 0) 잔여수량
  FROM bo
  LEFT OUTER JOIN po
    ON bo.bonumber = po.ponumber
  LEFT OUTER JOIN xo
    ON po.poseq = xo.poseq
  LEFT OUTER JOIN
       (SELECT jo.poseq
             , COUNT(*) cnt
          FROM jo
         INNER JOIN users u
            ON jo.usr = u.usr
         INNER JOIN employee e
            ON u.userseq = e.userseq
         INNER JOIN orgs o
            ON e.orgseq = o.orgseq
         WHERE o.orgcode NOT LIKE 'x%'
           AND e.userid  NOT LIKE 'x%'
         GROUP BY jo.poseq
        ) jo
    ON po.poseq = jo.poseq
 GROUP BY bo.boname
;

 

by 초보oracle [2016.03.23 19:09:17]

답변 감사합니다!!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입