상위 레벨 코드 가져오는 방법

sql바보 2020.06.22 15:19 Views : 526

아래 TABLE과 같은 자료가 있을때

상위 레벨의 품목코드를 가지고 오는 방법이 있을까요?

seq	Level	코드	품명
1	1	HC100A00001	HYDRAULIC CYLINDER UNIT COMPLETE
2	2	HC400700001	HCU BASE PLATE MACHINING
3	2	HC400700002	HCU BASE PLATE MACHINING
4	2	HC100Z00001	ASSEMBLY KIT
5	3	HC509900002	STUD
6	3	HC509900003	CAM LOCK WASHER
7	3	HC509900004	HEXAGON NUT
8	2	HC100B00001	HYDRAULIC CYLINDER UNIT
9	3	HC100C00001	DISTRIBUTOR BLOCK COMPLETE
10	4	HC300100001	DISTRIBUTOR BLOCK
11	5	HC600100001	DISTRIBUTOR BLOCK(소재)
12	4	HC509900004	SCREW PLUG
13	4	HC509900005	SCREW PLUG
14	4	HC509900006	SCREW PLUG
15	4	HC509900007	SCREW PLUG
16	4	HC509900008	PARALLEL PIN
17	4	HC409900001	ORIFICE
18	4	HC509900009	SCREW PLUG
19	4	HC509900010	PARALLEL PIN
20	4	HC409900002	ORIFICE
21	4	HC409900003	ORIFICE
22	4	HC509900011	SPRING PIN
23	4	HC509900012	SCREW PLUG
24	3	HC100D00001	HYDRAULIC EXHAUST ACTUATOR
25	4	HC509900013	HEXAGON SOCKET BOLT
26	4	HC509900004	SCREW PLUG
27	4	HC509900014	HEXAGON SOCKET BOLT
28	4	HC509900015	PARALLEL PIN
29	4	HC100Z00002	NON RETURN VALVE
30	5	HC409900004	NON RETURN VALVE
31	5	HC409900005	GASKET
32	4	HC409900006	SEALING RING
33	4	HC409900007	SEALING RING
34	4	HC300200001	ONE PIECE OIL CYLINDER
35	4	HC300300001	TOP PIECE OIL CYLINDER
36	4	HC400800001	STEP 2 PISTON
37	4	HC400900001	STEP 1 PISTON
38	4	HC401000001	BOTTOM PIECE OIL CYLINDER
39	3	HC100E00001	FUEL OIL PRESSURE BOOSTER
40	4	HC509900016	SHAFT RETAINING RINS
41	4	HC409900008	SHAPED PLATE
42	4	HC509900017	SCREW PLUG
43	4	HC509900018	HEXAGON SOCKET BOLT
44	4	HC409900009	UNION NUT & PLUG SCREW
45	4	HC509900019	HEXAGON BOLT
46	4	HC509900020	HEXAGON SOCKET BOLT
47	4	HC409900010	HYDRAULIC CYLINDER UNIT FLANGE
48	4	HC409900011	ORIFICE
49	4	HC409900012	ROUND PACKING

예를 들면 2번째 줄의 경우 1레벨 밑의 2레벨이므로 1레벨의 코드 HC100A00001 를 가져오고

행 40-49번의 4레벨의 경우 상위 3레벨인 HC100E00001 의 값을 가져와 각각 순번을 매기고 싶습니다.

아래 와 같이 출력을 하고 싶습니다.

좋은 방법이 있을까요?

결과값

seq	Level	코드	품명	모품목	순번
1	1	HC100A00001	HYDRAULIC CYLINDER UNIT COMPLETE	HC100A00001	001
2	2	HC400700001	HCU BASE PLATE MACHINING	HC100A00001	001
3	2	HC400700002	HCU BASE PLATE MACHINING	HC100A00001	002
4	2	HC100Z00001	ASSEMBLY KIT	HC100A00001	003
5	3	HC509900002	STUD	HC100Z00001	001
6	3	HC509900003	CAM LOCK WASHER	HC100Z00001	002
7	3	HC509900004	HEXAGON NUT	HC100Z00001	003
8	2	HC100B00001	HYDRAULIC CYLINDER UNIT	HC100A00001	004
9	3	HC100C00001	DISTRIBUTOR BLOCK COMPLETE	HC100B00001	001
10	4	HC300100001	DISTRIBUTOR BLOCK	HC100C00001	001
11	5	HC600100001	DISTRIBUTOR BLOCK(소재)	HC300100001	001
12	4	HC509900004	SCREW PLUG	HC100C00001	002
13	4	HC509900005	SCREW PLUG	HC100C00001	003
14	4	HC509900006	SCREW PLUG	HC100C00001	004
15	4	HC509900007	SCREW PLUG	HC100C00001	005
16	4	HC509900008	PARALLEL PIN	HC100C00001	006
17	4	HC409900001	ORIFICE	HC100C00001	007
18	4	HC509900009	SCREW PLUG	HC100C00001	008
19	4	HC509900010	PARALLEL PIN	HC100C00001	009
20	4	HC409900002	ORIFICE	HC100C00001	010
21	4	HC409900003	ORIFICE	HC100C00001	011
22	4	HC509900011	SPRING PIN	HC100C00001	012
23	4	HC509900012	SCREW PLUG	HC100C00001	013
24	3	HC100D00001	HYDRAULIC EXHAUST ACTUATOR	HC100B00001	002
25	4	HC509900013	HEXAGON SOCKET BOLT	HC100D00001	001
26	4	HC509900004	SCREW PLUG	HC100D00001	002
27	4	HC509900014	HEXAGON SOCKET BOLT	HC100D00001	003
28	4	HC509900015	PARALLEL PIN	HC100D00001	004
29	4	HC100Z00002	NON RETURN VALVE	HC100D00001	005
30	5	HC409900004	NON RETURN VALVE	HC100Z00002	001
31	5	HC409900005	GASKET	HC100Z00002	002
32	4	HC409900006	SEALING RING	HC100D00001	006
33	4	HC409900007	SEALING RING	HC100D00001	007
34	4	HC300200001	ONE PIECE OIL CYLINDER	HC100D00001	008
35	4	HC300300001	TOP PIECE OIL CYLINDER	HC100D00001	009
36	4	HC400800001	STEP 2 PISTON	HC100D00001	010
37	4	HC400900001	STEP 1 PISTON	HC100D00001	011
38	4	HC401000001	BOTTOM PIECE OIL CYLINDER	HC100D00001	012
39	3	HC100E00001	FUEL OIL PRESSURE BOOSTER	HC100B00001	003
40	4	HC509900016	SHAFT RETAINING RINS	HC100E00001	001
41	4	HC409900008	SHAPED PLATE	HC100E00001	002
42	4	HC509900017	SCREW PLUG	HC100E00001	003
43	4	HC509900018	HEXAGON SOCKET BOLT	HC100E00001	004
44	4	HC409900009	UNION NUT & PLUG SCREW	HC100E00001	005
45	4	HC509900019	HEXAGON BOLT	HC100E00001	006
46	4	HC509900020	HEXAGON SOCKET BOLT	HC100E00001	007
47	4	HC409900010	HYDRAULIC CYLINDER UNIT FLANGE	HC100E00001	008
48	4	HC409900011	ORIFICE	HC100E00001	009
49	4	HC409900012	ROUND PACKING	HC100E00001	010

I want to

cmd_document_vote_user Update Log List

Comment 1

루디먼트

2020.06.28 16:15

;with cteSample (seq,Level,코드,품명) as (
select 1,1,'HC100A00001','HYDRAULIC CYLINDER UNIT COMPLETE'
union all select 2,2,'HC400700001','HCU BASE PLATE MACHINING'
union all select 3,2,'HC400700002','HCU BASE PLATE MACHINING'
union all select 4,2,'HC100Z00001','ASSEMBLY KIT'
union all select 5,3,'HC509900002','STUD'
union all select 6,3,'HC509900003','CAM LOCK WASHER'
union all select 7,3,'HC509900004','HEXAGON NUT'
union all select 2,'HC100B00001','HYDRAULIC CYLINDER UNIT'
union all select 9,3,'HC100C00001','DISTRIBUTOR BLOCK COMPLETE'
union all select 10,4,'HC300100001','DISTRIBUTOR BLOCK'
union all select 11,5,'HC600100001','DISTRIBUTOR BLOCK(소재)'
union all select 12,4,'HC509900004','SCREW PLUG'
union all select 13,4,'HC509900005','SCREW PLUG'
union all select 14,4,'HC509900006','SCREW PLUG'
union all select 15,4,'HC509900007','SCREW PLUG'
union all select 16,4,'HC509900008','PARALLEL PIN'
union all select 17,4,'HC409900001','ORIFICE'
union all select 18,4,'HC509900009','SCREW PLUG'
union all select 19,4,'HC509900010','PARALLEL PIN'
union all select 20,4,'HC409900002','ORIFICE'
union all select 21,4,'HC409900003','ORIFICE'
union all select 22,4,'HC509900011','SPRING PIN'
union all select 23,4,'HC509900012','SCREW PLUG'
union all select 24,3,'HC100D00001','HYDRAULIC EXHAUST ACTUATOR'
union all select 25,4,'HC509900013','HEXAGON SOCKET BOLT'
union all select 26,4,'HC509900004','SCREW PLUG'
union all select 27,4,'HC509900014','HEXAGON SOCKET BOLT'
union all select 28,4,'HC509900015','PARALLEL PIN'
union all select 29,4,'HC100Z00002','NON RETURN VALVE'
union all select 30,5,'HC409900004','NON RETURN VALVE'
union all select 31,5,'HC409900005','GASKET'
union all select 32,4,'HC409900006','SEALING RING'
union all select 33,4,'HC409900007','SEALING RING'
union all select 34,4,'HC300200001','ONE PIECE OIL CYLINDER'
union all select 35,4,'HC300300001','TOP PIECE OIL CYLINDER'
union all select 36,4,'HC400800001','STEP 2 PISTON'
union all select 37,4,'HC400900001','STEP 1 PISTON'
union all select 38,4,'HC401000001','BOTTOM PIECE OIL CYLINDER'
union all select 39,3,'HC100E00001','FUEL OIL PRESSURE BOOSTER'
union all select 40,4,'HC509900016','SHAFT RETAINING RINS'
union all select 41,4,'HC409900008','SHAPED PLATE'
union all select 42,4,'HC509900017','SCREW PLUG'
union all select 43,4,'HC509900018','HEXAGON SOCKET BOLT'
union all select 44,4,'HC409900009','UNION NUT & PLUG SCREW'
union all select 45,4,'HC509900019','HEXAGON BOLT'
union all select 46,4,'HC509900020','HEXAGON SOCKET BOLT'
union all select 47,4,'HC409900010','HYDRAULIC CYLINDER UNIT FLANGE'
union all select 48,4,'HC409900011','ORIFICE'
union all select 49,4,'HC409900012','ROUND PACKING'
)
select *
from cteSample as a
outer apply (
select top 1 코드, level
from cteSample
where level < a.level
order by seq desc
) as b

cmd_comment_vote_user Upvote0 Downvote0

No.	Subject	Author	Date	Views
Notice	2023년 1월 - SQLER의 업데이트 강좌 리스트	코난(김대우)	2023.01.02	2148
10609	SP 내부의 트랜젝션 처리	코코	2020.06.22	526
»	상위 레벨 코드 가져오는 방법 [1]	sql바보	2020.06.22	526
10607	mssql 프로지져 관련 문의 드립니다.	hoon	2020.06.22	407
10606	대용량 DB 질문 [2]	하이욧	2020.06.22	692
10605	대용량 데이터 삭제 문의 입니다. [1]	인버터	2020.06.20	438
10604	서브쿼리를 이용해서 다수의 매개변수를 받아 update를 진행하고 있습니다... [4]	남중남고군대공대	2020.06.15	575
10603	쿼리 질문입니다. [1]	희망나라	2020.06.12	498
10602	조건에 따라 Sum값을 - 로 표시하고 싶습니다. [1]	hsp-user	2020.06.10	416
10601	데이터베이스 컬럼에 관한 질문입니다 [2]	병든토끼	2020.06.04	463
10600	스토어드 프로시저 내에서 동적쿼리, 동적쿼리 내에서 스토어드 프로시저? [4]	남중남고군대공대	2020.06.03	548
10599	DataBase 병합 방법	안돼요	2020.06.02	425
10598	쿼리문이 오래 걸리는 이유 [1]	풍란올	2020.06.01	759
10597	SQL SERVER 2008 R2 STAND 버전에서의 감사 관련 질문입니다.	김삐씨	2020.05.28	441
10596	sql 질문입니다. 테이블 보고 문제 만들기	markiscapable	2020.05.28	425
10595	각 제품의 카테고리별로 가격이 비싼 제품을 알려고합니다.	markiscapable	2020.05.28	365
10594	중첩 JOIN문 질문드립니다. [1]	smyoon	2020.05.26	570
10593	SQL2005 -> SQL2016 마이그레이션 관련 질문	오희택	2020.05.25	431
10592	SP 실행중 lock이 발생하는데 SSMS에서 발생했다고 나오는데 모르겠습니다.	나노큐브	2020.05.25	443
10591	SQL BIND 변수 질문입니다. [1]	마이노키	2020.05.24	405
10590	무한카테고리 설계와 제품등록 [5]	다자녀아빠	2020.05.22	692

Write Tag

First Page 11 12 13 14 15 16 17 18 19 20 Last Page

상위 레벨 코드 가져오는 방법

Comment 1

루디먼트

커뮤니티

데이터베이스 개발

클라우드 개발

머신러닝/AI개발

파이썬&오픈소스 개발