MSSQL 빈 값을 바로 위 데이터로 채우기

Question

다음과 같이 데이터가 저장되는 테이블이 있는데 조회 시Row 가 1~7 까지는 Row 0의 데이터인 <003001>Row 가 9~11 까지는 Row 8의 데이터인 <003002> 이런식으로 채우려면 어떤 부분을 공부해야 될까요?ISNULL 검사해서 '' 이면 위에 Row에서 조회를 해오려고 했는데 그렇게 했을 땐 Row 0의 값을 Row 1에는 넣어주는데 Row 2부터는 Row 1의 값이 빈 걸로 되어 있으니잘 안되네요ㅠㅠ

antop · Accepted Answer

저 같은 경우 일단 select 해서 자바 쪽에서 해결 하겠습니다. (제가 자바 개발자라 -_-) import java.util.Arrays; import java.util.Collections; import java.util.List; import java.util.function.IntConsumer; import java.util.stream.IntStream; public class Test { public static void main(String[] args) { // sample data List list = Arrays.asList(new Data[] { new Data(59671, 0, "<0003001>"), new Data(59671, 1, null), new Data(59671, 2, null), new Data(59671, 3, null), new Data(59671, 4, null), new Data(59671, 5, null), new Data(59671, 6, null), new Data(59671, 7, null), new Data(59671, 8, "<0003002>"), new Data(59671, 9, null), new Data(59671, 10, null), new Data(59671, 11, null), new Data(59671, 12, "<0003003>"), new Data(59671, 13, null), new Data(59671, 14, null), new Data(59671, 15, null), new Data(59671, 16, null) }); // 정렬이 안되어 있을 경우 정렬 Collections.sort(list, (o1, o2) -> o1.getDocumentNo() - o2.getDocumentNo() != 0 ? o1.getDocumentNo() - o2.getDocumentNo() : o1.getRow() - o2.getRow()); System.out.println("-- before --"); list.forEach(System.out::println); // 하나씩 돌리면서 빈 데이터는 이전 데이터 값으로 채우기 // stream IntStream stream = IntStream.range(0, list.size()); stream.forEach(new IntConsumer() { @Override public void accept(int value) { if (value == 0) { return; } Data current = list.get(value); if (current.getaCol() == null) { Data before = list.get(value - 1); current.setaCol(before.getaCol()); } } }); /* // for int size = list.size(); for (int i = 0 ; i < size ; i++) { if (i == 0) { continue; } Data current = list.get(i); if (current.getaCol() == null) { Data before = list.get(i - 1); current.setaCol(before.getaCol()); } } */ System.out.println("-- after --"); list.forEach(System.out::println); } public static class Data { private int documentNo; private int row; private String aCol; public Data() { } public Data(int documentNo, int row, String aCol) { super(); this.documentNo = documentNo; this.row = row; this.aCol = aCol; } public int getDocumentNo() { return documentNo; } public void setDocumentNo(int documentNo) { this.documentNo = documentNo; } public int getRow() { return row; } public void setRow(int row) { this.row = row; } public String getaCol() { return aCol; } public void setaCol(String aCol) { this.aCol = aCol; } @Override public String toString() { return "Data [documentNo=" + documentNo + ", row=" + row + ", aCol=" + aCol + "]"; } } }

plwtcs · Answer

커서로 돌리기엔 데이터가 3500건 정도 되는 경우도 있는데 실시간 조회를 하는 곳이라서 다른 방법을 찾고 있습니다..

plwtcs · Answer

antop 저도 한 번 프로그램 단에서 테스트 해봐야겠습니다. 감사합니다!

HelloHelloo · Answer

with aa (documentno, row, acol) as ( select '59671', 0, '<003001>' union all select '59671', 1, '' union all select '59671', 2, '' union all select '59671', 3, '' union all select '59671', 4, '' union all select '59671', 5, '' union all select '59671', 6, '' union all select '59671', 7, '' union all select '59671', 8, '<003002>' union all select '59671', 9, '' union all select '59671', 10, '' union all select '59671', 11, '' union all select '59671', 12, '<003003>' union all select '59671', 13, '' union all select '59671', 14, '' union all select '59671', 15, '' union all select '59671', 16, '' ) -- select * from aa ,bb (documentno, row, acol, idx) as ( select documentno, row, acol, ROW_NUMBER() over(partition by documentno order by row) as idx from aa where isnull(acol, '') <> '' ) -- select * from bb ,cc (documentno, row, idx) as ( select a.documentno, a.row, max(a.idx) over(partition by a.documentno order by a.row) as idx from ( select aa.documentno, aa.row, aa.acol, bb.idx from aa left join bb on aa.documentno = bb.documentno and aa.row = bb.row ) as a ) -- select * from cc select cc.documentno, cc.row, bb.acol from cc left join bb on cc.documentno = bb.documentno and cc.idx = bb.idx order by 1, 2 만약 다음 [row]에 [acol]에 들어갈 값이 이전에 들어간 값보다 무조건 클때 with aa (documentno, row, acol) as ( select '59671', 0, '<003001>' union all select '59671', 1, '' union all select '59671', 2, '' union all select '59671', 3, '' union all select '59671', 4, '' union all select '59671', 5, '' union all select '59671', 6, '' union all select '59671', 7, '' union all select '59671', 8, '<003002>' union all select '59671', 9, '' union all select '59671', 10, '' union all select '59671', 11, '' union all select '59671', 12, '<003003>' union all select '59671', 13, '' union all select '59671', 14, '' union all select '59671', 15, '' union all select '59671', 16, '' ) select documentno, row, max(acol) over(partition by documentno order by row) as acol from aa order by 1, 2이런식으로 줄여도 될거 같아요. ps. 분명 더 좋은 방법이 있을거에요!

날아라치킨 · Answer

set nocount on if object_id('tempdb..#result') is not null DROP TABLE #result create table #result ( DocumentNo int, Row int, ACol varchar(20) ) declare @r int declare @value varchar(20) declare @ivalue int set @ivalue=3001 declare @i int set @i=1 while (@i<4000) begin set @r=(rand()*90) if (@i=1) set @r=80 if (@r>=80) begin set @value='<' + format(@ivalue,'000000') + '>' set @ivalue=@ivalue+1 end else begin set @value=null end INSERT INTO #result (DocumentNo,Row,ACol) select 59671,@i,@value set @i=@i+1 end ;with resource as ( select a.Acol, a.Row as First, isnull(lead(a.Row,1) over (order by Acol)-1,(select max(Row) from #Result)) as Last from ( select a.Acol, max(Row) as Row from #result as A where a.ACol is not null group by a.Acol ) as A ) select a.DocumentNo, a.Row, b.Acol from #result A outer apply ( select sa.Acol from resource as SA where sa.First<=a.Row and sa.Last>=a.Row ) as B order by a.Row

plwtcs · Answer

HelloHelloo WITH로 선언 한 뒤에 안에서는 Order by가 안되나봐요ㅠㅠ 그래서 아래 쿼리처럼 Row를 따로 만들었어요감사합니다 ㅎㅎ 다른 분들도 정말 감사합니다!! (--)(__) WITHAA (DocumentNo, ROW, ACol) AS( SELECT DocumentNo, (ROW_NUMBER() OVER (ORDER BY CONVERT(INT,ROW)) - 1) as ROW, ACol FROM '테이블' WITH(NOLOCK) WHERE DOCUMENTNO &#61; 59671)SELECT DOCUMENTNO ,ROW ,MAX(ACol) OVER (PARTITION BY DOCUMENTNO ORDER BY ROW) AS AColFROM AA

MSSQL 빈 값을 바로 위 데이터로 채우기

6개의 답변

#row 목록