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

sql바보 2020.06.22 15:19 Views : 592

아래 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	SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트)	코난(김대우)	2023.08.18	36266
Notice	Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트)	코난(김대우)	2021.01.01	18865
10623	쿼리 결과를 서버로 올릴때 질문입니다. [2]	희망나라	2020.07.20	548
10622	차등 백업이 풀백업 이후 한번만 진행되는 현상 [1]	레몽	2020.07.20	595
10621	CONVERT 함수 문의드립니다. [5]	초보SQL러	2020.07.20	544
10620	테이블에서 여러가지 조건 검색 질문 좀 할게요 [3]	엘리사	2020.07.17	541
10619	저기요...sql비교연산자에서요.... ☞☜ [1]	김포천재	2020.07.16	552
10618	테이블상 동일한 여러개의 로우에 조건을 검색하여 데이터를 찾을수있을까요? [1]	뽀구	2020.07.15	436
10617	중복시간을 제외한 시간의 합을 구할수있을까요? [7]	톰슨	2020.07.14	928
10616	SQL 서버 연결 [1]	망태기군	2020.07.13	1322
10615	SQL 특정 시간 날짜 Sum 문의 입니다. [1]	핸젤루	2020.07.07	791
10614	쿼리 질문합니다...	병든토끼	2020.07.07	415
10613	db 백업? 이중화? 질문 [2]	하이욧	2020.07.06	544
10612	인덱스를 위한 교재 추천 부탁 드립니다. [2]	대두개발자	2020.06.29	658
10611	2개의 테이블을 union all 로 조회 후 합치고 싶습니다. [2]	초보SQL러	2020.06.25	529
10610	사용자 이(가) 로그인하지 못했습니다. 원인: 명시적으로 지정된 데이터베이스를 열지 못했습니다.	마징고	2020.06.24	558
10609	SP 내부의 트랜젝션 처리	코코	2020.06.22	638
»	상위 레벨 코드 가져오는 방법 [1]	sql바보	2020.06.22	592
10607	mssql 프로지져 관련 문의 드립니다.	hoon	2020.06.22	436
10606	대용량 DB 질문 [2]	하이욧	2020.06.22	815
10605	대용량 데이터 삭제 문의 입니다. [1]	인버터	2020.06.20	505
10604	서브쿼리를 이용해서 다수의 매개변수를 받아 update를 진행하고 있습니다... [4]	남중남고군대공대	2020.06.15	651

Write Tag

First Page 13 14 15 16 17 18 19 20 21 22 Last Page

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

Comment 1

루디먼트

커뮤니티

데이터베이스 개발

클라우드 개발

머신러닝/AI개발

파이썬&오픈소스 개발