데이터베이스 질문과 답변

QUERY : 4개 테이블 조인 그 중 하나만 limit?? 0 9 3,882

by dkfldkfl [SQL Query] [2018.08.14 13:23:45] 

SELECT
  a.GID,
  a.ITEM_ID,
  a.BOM_CNT,
  a.BOM_VERSION,
  b.*,
  (select c.COST
   from TB_MATERIAL_COST_INFO as c
   where c.ITEM_ID = b.ITEM_ID
   limit 0, 1)          as COST,
  (select d.S_NAME
   from TB_SUBCONTRACTOR_INFO as d
   where d.SID = b.SID) as s_name

FROM TB_PRODUCTION_MATERIAL as a inner join TB_MATERIAL_INFO as b

WHERE a.ITEM_ID = b.ITEM_ID;

 

이런 쿼리를 조인으로 바꾸려고 합니다

4개 테이블이라서 각각 조인 걸어주는데 문제는 중간에 limit 으로 한개만 가져오는 부분이 있습니다

select c.COST from TB_MATERIAL_COST_INFO as c where c.ITEM_ID = b.ITEM_ID limit 0, 1)

 

이게 중간에 껴있어서 WHERE 절에서 어떤 조건을 추가해야할지 모르겠습니다.

그러니까 ITEM_ID 가 같은 것을 TB_MATERIAL_COST_INFO 와 TB_MATERIAL_INFO 에서 가져오는데

TB_MATERIAL_COST_INFO 는  한 아이템에 대한 변동 내역이 모두 저장되어 있습니다

그래서 최근 한개만 가져오는 상황입니다

 

SELECT
  a.GID,
  a.ITEM_ID,
  a.BOM_CNT,
  a.BOM_VERSION,
  b.*,
  c.COST as COST,
  d.S_NAME as s_name

FROM TB_PRODUCTION_MATERIAL as a, TB_MATERIAL_INFO as b, TB_MATERIAL_COST_INFO as c, TB_SUBCONTRACTOR_INFO as d

WHERE a.ITEM_ID = b.ITEM_ID and c.ITEM_ID = b.ITEM_ID and d.SID = b.SID;

 

요기에 어떻게 WHERE 절을 넣어야할까요?? 감이 안잡힙니다 ㅠ

by 우리집아찌 [2018.08.14 13:31:38]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 요건이 충분치 않네요. 맘대로 짰습니다.
SELECT
  a.GID,
  a.ITEM_ID,
  a.BOM_CNT,
  a.BOM_VERSION,
  b.*,
  C.COST,
  D.S_NAME
FROM TB_PRODUCTION_MATERIAL a
   , TB_MATERIAL_INFO b
   , ( SELECT ITEM_ID , MAX(COST) COST FROM TB_MATERIAL_COST_INFO GROUP BY ITEM_ID ) c    -- COST값이 여러개일경우 어느값을 가져올지 정의해야함  ex) min , max , 또는 분석함수 이용..
   , ( SELECT SID , MAX(S_NAME) S_NAME FROM TB_SUBCONTRACTOR_INFO GROUP BY SID )d         -- S_NAME값이 여러개일경우 어느값을 가져올지 정의해야함
WHERE a.ITEM_ID = b.ITEM_ID
  AND a.ITEM_ID = c.ITEM_ID
  AND b.SID     = d.SID

 

by dkfldkfl [2018.08.14 13:48:38]

답변 감사합니다

 

ITEM_ID   COST

 ABC        1000

 ABC        2000

 ABC        3000

 ABC        2000

 

DB에 이렇게 저장이 되었다면 요기서 가져와야하는 값은 맨 밑에 있는 값인 2000 입니다 (최근 데이터)

그런데 이 한테이블 내에서 내부에서 그룹으로 묶을때 맨 마지막꺼만 적용 이런 방법이 있을까요?

 

ABC 는 2000 이 나오고

BBB 3000

BBB 4000

CCC 1000

도 있다면 BBB = 4000 CCC = 1000 이렇게요

 

by 우리집아찌 [2018.08.14 13:56:59]

마지막 데이터를 분류할수 있는 컬럼이 있어야합니다.

mysql 버젼이 분석함수를 지원한다면

?
1
2
3
4
5
6
7
-- 이런식으로 인라인뷰를 원하시는 cost를 먼저 구하고 join 하시면 됩니다.
 
select *
  from (select item_id , cost , row_number() over(order by 정렬컬럼) rn
             from tb_material_cost_info 
          )
  where rn =1 

 

by dkfldkfl [2018.08.14 13:59:19] 
 SELECT ITEM_ID , COST FROM (SELECT ITEM_ID,COST, REGDT FROM TB_MATERIAL_COST_INFO Order by REGDT DESC ) as Z GROUP BY ITEM_ID

 

이렇게 하는건 어떤가요? 된것같긴합니다

그룹바이로 묶으면 제일 첫번째꺼만 걸리니까 이렇게 해도되는것같습니다

by 우리집아찌 [2018.08.14 14:31:53]
?
1
2
3
4
5
SELECT ITEM_ID
     , COST
  FROM (SELECT ITEM_ID , COST , ROW_NUMBER() OVER(PARTITION BY ITEM_ID ORDER BY REGDT DESC ) RN 
         FROM TB_MATERIAL_COST_INFO ) Z
 WHERE RN = 1

 

by dkfldkfl [2018.08.14 14:55:05]

이거 혹시 오라클 언어인가요?

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY ITEM_ID ORDER BY REGDT DESC ) RN

 

이런 에러가 뜹니다

 

 

 

SELECT
  a.GID,
  a.ITEM_ID,
  a.BOM_CNT,
  a.BOM_VERSION,
  b.*,
  c.COST,
  d.S_NAME
FROM TB_PRODUCTION_MATERIAL a
  , TB_MATERIAL_INFO b
  , (SELECT ITEM_ID
       , COST
     FROM (SELECT ITEM_ID , COST , ROW_NUMBER() OVER(PARTITION BY ITEM_ID ORDER BY REGDT DESC ) RN
           FROM TB_MATERIAL_COST_INFO ) Z
     WHERE RN = 1) c    -- COST값이 여러개일경우 어느값을 가져올지 정의해야함  ex) min , max , 또는 분석함수 이용..
  , TB_SUBCONTRACTOR_INFO as d         -- S_NAME값이 여러개일경우 어느값을 가져올지 정의해야함
WHERE a.ITEM_ID = b.ITEM_ID
      AND a.ITEM_ID = c.ITEM_ID
      AND b.SID     = d.SID

 

실행은 이걸로했습니다

by 우리집아찌 [2018.08.14 15:05:42]

MY-SQL 이나 마리아DB에서도 신규 버젼에서는  분석함수 지원을 다합니다.

사용버젼이 지원되는지 확인해보시면 됩니다.

by 우리집아찌 [2018.08.14 15:07:02]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 이렇게 하셔도 됩니다.
SELECT
  a.GID,
  a.ITEM_ID,
  a.BOM_CNT,
  a.BOM_VERSION,
  b.*,
  c.COST,
  d.S_NAME
FROM TB_PRODUCTION_MATERIAL a
   , TB_MATERIAL_INFO b
   ,(SELECT ITEM_ID
          , COST
          , ROW_NUMBER() OVER(PARTITION BY ITEM_ID ORDER BY REGDT DESC ) RN
     FROM TB_MATERIAL_COST_INFO ) C    -- COST값이 여러개일경우 어느값을 가져올지 정의해야함  ex) min , max , 또는 분석함수 이용..
  , TB_SUBCONTRACTOR_INFO as d         -- S_NAME값이 여러개일경우 어느값을 가져올지 정의해야함
WHERE a.ITEM_ID = b.ITEM_ID
      AND a.ITEM_ID = c.ITEM_ID
      AND b.SID     = d.SID 
      AND C.RN = 1

 

by 마농 [2018.08.17 15:03:29]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT a.gid
     , a.item_id
     , a.bom_cnt
     , a.bom_version
     , b.*
     , c.cost   AS cost
     , d.s_name AS s_name
  FROM tb_production_material a
 INNER JOIN tb_material_info  b
    ON a.item_id = b.item_id
  LEFT OUTER JOIN tb_subcontractor_info  d
    ON b.sid = d.sid
  LEFT OUTER JOIN tb_material_cost_info c
    ON b.item_id = c.item_id
  LEFT OUTER JOIN tb_material_cost_info e
    ON c.item_id = e.item_id
   AND c.regdt   < e.regdt
 WHERE e.item_id IS NULL
;

 

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