결과치에 맞춰 날코딩된 쿼리이니..

너그러이 양해를 부탁드립니다-_-;;;

조건을 분석한 결과

1) 2열보다 큰 경우 -> 해당열 - 1 의 데이터와 비교

2) 1열인 경우 -> 해당열 + 1 의 데이터와 비교

상기 2가지의 조건으로 정리되는듯 하더군요..

실력이 미비하여 쿼리가 복잡하고..

또한 결과물만을 내기 위한 쿼리임을 다시 한번

재차 강조합니다..

참고만 하세요..(안될 가능성이 더 높음-_-;)

---쿼리시작---

;with tblA( midx, session , IP ) As
(
      Select 1, 123123 ,  '192.168.10.1'  Union All
      Select 2, 1231231,  '192.168.10.2'  Union All
      Select 3, 123123 ,  '192.168.100.1' Union All
      Select 4, 123123 ,  '192.168.10.1'  Union All
      Select 5, 123123 ,  '192.168.101.3'
)
,tblB ( midx,session,IP,C_Class) As
(
      Select a.*
            ,SubString(a.IP,1,CHARINDEX('.',a.IP,9)-1)
        From tblA a
)
,tblC ( midx,session,IP,C_Class,seq) As
(
      Select a.*
            ,ROW_NUMBER() Over (Order By a.IP Asc,a.C_Class Asc,a.midx Asc)
        From tblB a
)
     Select a.midx
           ,a.session
           ,a.IP
           ,a.C_Class
           ,(
               Case When a.seq = 1
                       Then
                            (
                                 Case When ISNULL(d.IP,'') = '' And ISNULL(e.IP,'') = ''
                                         Then '불일치'
                                      When ISNULL(d.IP,'') <> ''
                                         Then '일치'
                                      When ISNULL(e.IP,'') <> '' And ISNULL(d.IP,'') = ''
                                         Then '클래스일치'
                                      Else '불일치'
                                 End
                            )
                    Else            
                            (            
                                 Case When ISNULL(b.IP,'') = '' And ISNULL(c.IP,'') = ''
                                         Then '불일치'
                                      When ISNULL(b.IP,'') <> ''
                                         Then '일치'
                                      When ISNULL(c.IP,'') <> '' And ISNULL(b.IP,'') = ''
                                         Then '클래스일치'
                                      Else '불일치'
                                 End
                            )
               End           
            )   
                 As duplicate
       From tblC a
                   Left Outer Join tblC b On b.seq + 1 = a.seq
                                         And a.IP = b.IP
                   Left Outer Join tblC c On c.seq + 1 = a.seq
                                         And a.C_Class = c.C_Class                                        
                                         And a.IP <> c.IP
                   Left Outer Join tblC d On d.seq - 1 = a.seq
                                         And a.IP = d.IP                                        
                   Left Outer Join tblC e On e.seq - 1 = a.seq
                                         And a.C_Class = e.C_Class                                        
                                         And a.IP <> e.IP
                                        
                                        
---쿼리끝---