데이터베이스 질문과 답변

쿼리 질문입니다 0 1 860

by 후인구 [SQL Query] [2022.07.18 21:10:08]

APSEQ APDTLSEQ SEQ STATUS EMPNO OPIN APPRDT
C515 1 7 01 10010 결재요청3 20210213
C515 3 7 03 10020 걸재승인3 20210213
C515 4 7 00 10030 　 　
C515 1 6 01 10010 결재요청3 20210213
C515 3 6 00 10020 　 　
C515 4 6 00 10030 　 　
C515 1 5 01 10010 결재요청2 20210211
C515 3 5 09 10020 결재반려2 20210211
C515 4 5 00 10030 　 　
C515 1 4 01 10010 결재요청2 20210211
C515 3 4 00 10020 　 　
C515 4 4 00 10030 　 　
C515 1 3 01 10010 결재요청1 20210210
C515 3 3 03 10020 결재승인1 20210210
C515 4 3 09 10030 결재반려1 20210210
C515 1 2 01 10010 결재요청1 20210210
C515 3 2 03 10020 결재승인1 20210210
C515 4 2 00 10030 　 　
C515 1 1 01 10010 결재요청1 20210210
C515 3 1 00 10020 　 　
C515 4 1 00 10030 　 　

현재는 저장 할때마다 3 Rows 씩 저장되고 있습니다.

 

원하는 결과는 결재 1세트가 끝나면 아래와 같이 나오게 하려고 합니다.  (SEQ=7, SEQ=5, SEQ=3)

APSEQ APDTLSEQ SEQ STATUS EMPNO OPIN APPRDT
C515 1 7 01 10010 결재요청3 20210213
C515 3 7 03 10020 걸재승인3 20210213
C515 4 7 00 10030 　 　
C515 1 5 01 10010 결재요청2 20210211
C515 3 5 09 10020 결재반려2 20210211
C515 4 5 00 10030 　 　
C515 1 3 01 10010 결재요청1 20210210
C515 3 3 03 10020 결재승인1 20210210
C515 4 3 09 10030 결재반려1 20210210

 

맨 마지막 SEQ는 무조건 나와야 합니다.

C515 1 7 01 10010 결재요청3 20210213
C515 3 7 03 10020 결재승인3 20210213
C515 4 7 00 10030 　 　

TABLE 명 : TB_APPR

고수님들의 조언 부탁드립니다

by 마농 [2022.07.19 07:26:45]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
WITH t AS
(
SELECT 'C515' apseq, 1 apdtlseq, 7 seq, '01' status, '10010' empno, '결재요청3' opin, '20210213' apprdt FROM dual
UNION ALL SELECT 'C515', 3, 7, '03', '10020', '걸재승인3', '20210213' FROM dual
UNION ALL SELECT 'C515', 4, 7, '00', '10030', ''         , ''         FROM dual
UNION ALL SELECT 'C515', 1, 6, '01', '10010', '결재요청3', '20210213' FROM dual
UNION ALL SELECT 'C515', 3, 6, '00', '10020', ''         , ''         FROM dual
UNION ALL SELECT 'C515', 4, 6, '00', '10030', ''         , ''         FROM dual
UNION ALL SELECT 'C515', 1, 5, '01', '10010', '결재요청2', '20210211' FROM dual
UNION ALL SELECT 'C515', 3, 5, '09', '10020', '결재반려2', '20210211' FROM dual
UNION ALL SELECT 'C515', 4, 5, '00', '10030', ''         , ''         FROM dual
UNION ALL SELECT 'C515', 1, 4, '01', '10010', '결재요청2', '20210211' FROM dual
UNION ALL SELECT 'C515', 3, 4, '00', '10020', ''         , ''         FROM dual
UNION ALL SELECT 'C515', 4, 4, '00', '10030', ''         , ''         FROM dual
UNION ALL SELECT 'C515', 1, 3, '01', '10010', '결재요청1', '20210210' FROM dual
UNION ALL SELECT 'C515', 3, 3, '03', '10020', '결재승인1', '20210210' FROM dual
UNION ALL SELECT 'C515', 4, 3, '09', '10030', '결재반려1', '20210210' FROM dual
UNION ALL SELECT 'C515', 1, 2, '01', '10010', '결재요청1', '20210210' FROM dual
UNION ALL SELECT 'C515', 3, 2, '03', '10020', '결재승인1', '20210210' FROM dual
UNION ALL SELECT 'C515', 4, 2, '00', '10030', ''         , ''         FROM dual
UNION ALL SELECT 'C515', 1, 1, '01', '10010', '결재요청1', '20210210' FROM dual
UNION ALL SELECT 'C515', 3, 1, '00', '10020', ''         , ''         FROM dual
UNION ALL SELECT 'C515', 4, 1, '00', '10030', ''         , ''         FROM dual
)
SELECT a.*
  FROM t a
     , (SELECT apseq, opin
             , MAX(seq) seq
          FROM t
         WHERE apdtlseq = 1
         GROUP BY apseq, opin
        ) b
 WHERE a.apseq = b.apseq
   AND a.seq   = b.seq
;

 

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