로

mssql 중복 데이터 질문인데요 부탁좀 드릴꼐요 ㅜㅜ 계속 삽질이네요

  select distinct(SVUSERID) 
   , Row_Number() over(order by CAST(SVDT AS float)) as RN
   , STFSEQ
   , PBLSH
   , (select CODNAME from FN_COD_MST where GRID='1011' and COD=PBLSH) PBLSHCODE   
   , STFNAME
   , ZIPCODE
   , ADRESS1 + ' ' + ADRESS2 as ADRESS
   , ADRESS2
   , TELNO1 + '-' + TELNO2 + '-' + TELNO3 as TELNO
   , PNAME
   , EMAIL
   , JSSEQ
   , CAST(SVDT AS float) SVDTi
     from FN_STF_MST
  where PBLSH in (select COD from FN_COD_MST where GRID='1011' and CODNAME like '%' + '골드' + '%')
  order by SVDTi
 
위와 같은 쿼리 실행시 결과가
 
table.JPG?type=w620
 
위와 같이 나옵니다.   
 
off1 
off3
off1 
순서로 3개의 레코드가 나옵니다.
 
이때
 
off1
off3
 
2개의 레코드만 나오게하고 싶은데 부탁 드릴께요
DISTINCT, GROUP BY 다 해봤는데
 
첫번째 off1과 세번째 off1은 SVUSERID 빼고는 내용이 모두 달라서 안되는 군요
 
즉 다음 그림과 같이 나오게 하고 싶습니다.
table2.JPG?type=w620 

댓글 1개

자문자답이네요


select SVUSERID
, Row_Number() over(order by CAST(SVDT AS float)) as RN
, STFSEQ
, PBLSH
, (select CODNAME from FN_COD_MST where GRID='1011' and COD=PBLSH) PBLSHCODE
, STFNAME
, ZIPCODE
, ADRESS1 + ' ' + ADRESS2 as ADRESS
, ADRESS2
, TELNO1 + '-' + TELNO2 + '-' + TELNO3 as TELNO
, PNAME
, EMAIL
, JSSEQ
, SVDT
from FN_STF_MST
where PBLSH in (select COD from FN_COD_MST where GRID='1011' and CODNAME like '%' + '골드' + '%')
AND SVUSERID not in (
select SVUSERID
from FN_STF_MST
where PBLSH in (select COD from FN_COD_MST where GRID='1011' and CODNAME like '%' + '골드' + '%')
GROUP BY SVUSERID
having count(SVUSERID) > 1
)
OR STFSEQ in(
select top 1 STFSEQ
from FN_STF_MST
where SVUSERID =
( select SVUSERID
from FN_STF_MST
where PBLSH in (select COD from FN_COD_MST where GRID='1011' and CODNAME like '%' + '골드' + '%')
GROUP BY SVUSERID
having count(SVUSERID) > 1
) order by SVDT
)

다른 의견 있으신분 부탁드립니다.
댓글을 작성하시려면 로그인이 필요합니다.

프로그램

8,187건
+
제목 글쓴이 날짜 조회
12-04-24 조회 2,657
12-04-24 조회 2,723
12-04-24 조회 3,876
12-04-24 조회 3,017
12-04-24 조회 3,366
12-04-24 조회 2,756
12-04-24 조회 3,274
12-04-24 조회 2,800
12-04-24 조회 2,779
12-04-24 조회 2,965
12-04-24 조회 4,653
12-04-23 조회 3,100
12-04-23 조회 3,914
12-04-23 조회 2,725
12-04-23 조회 2,733
12-04-23 조회 3,005
12-04-23 조회 2,648
12-04-23 조회 3,685
12-04-23 조회 2,574
12-04-23 조회 2,649
12-04-23 조회 3,000
12-04-23 조회 2,808
12-04-23 조회 2,657
12-04-23 조회 2,853
12-04-23 조회 2,853
12-04-23 조회 2,609
12-04-23 조회 4,605
12-04-23 조회 3,631
12-04-23 조회 3,500
12-04-22 조회 2,633
12-04-22 조회 2,594
12-04-22 조회 2,676
12-04-22 조회 2,890
12-04-22 조회 2,691
12-04-22 조회 3,303
12-04-22 조회 3,343
12-04-22 조회 2,937
12-04-21 조회 3,194
12-04-21 조회 4,138
12-04-21 조회 4,573
12-04-21 조회 7,902
12-04-21 조회 2,714
12-04-21 조회 3,112
12-04-21 조회 2,613
12-04-21 조회 2,853
12-04-20 조회 3,655
12-04-20 조회 3,159
12-04-20 조회 2,831
12-04-20 조회 2,915
12-04-20 조회 2,713
12-04-20 조회 2,836
12-04-20 조회 3,965
12-04-20 조회 4,183
12-04-20 조회 3,042
12-04-20 조회 3,292
12-04-20 조회 4,394
12-04-20 조회 3,333
12-04-19 조회 3,388
12-04-19 조회 3,026
12-04-18 조회 4,455
12-04-18 조회 2,917
12-04-18 조회 3,153
12-04-17 조회 3,419
12-04-17 조회 3,589
12-04-17 조회 5,188
12-04-17 조회 5,489
12-04-17 조회 2,918
12-04-17 조회 3,421
12-04-16 조회 2,895
12-04-16 조회 3,616
12-04-16 조회 3,316
12-04-16 조회 3,200
12-04-16 조회 3,615
12-04-16 조회 2,846
12-04-16 조회 4,426
12-04-15 조회 4,004
12-04-14 조회 3,690
12-04-14 조회 3,082
12-04-14 조회 3,746
12-04-14 조회 3,798
12-04-13 조회 3,435
12-04-13 조회 4,630
12-04-13 조회 3,230
12-04-13 조회 2,953
12-04-13 조회 3,173
12-04-13 조회 6,252
12-04-12 조회 4,870
12-04-12 조회 3,404
12-04-11 조회 3,630
12-04-10 조회 5,111
12-04-10 조회 4,063
12-04-10 조회 3,174
12-04-09 조회 3,218
12-04-09 조회 4,790
12-04-09 조회 3,145
12-04-08 조회 3,479
12-04-07 조회 2,810
12-04-06 조회 2,957
12-04-06 조회 3,711
12-04-06 조회 5,491