亂馬客

有時SQL出來的結果會有重覆的資料，如果要呈現在Report上的話，Report Tool都可設定第1筆資料後的資料顯示空白，或是用Group來處理。

也可以在SQL中處理，先建立測試資料如下(借用Terry的範例)，

use tempdb
go
if OBJECT_ID('dbo.sadf') is not null
    drop table dbo.sadf
go

create table sadf
(sam_key varchar(10), sam_dte  varchar(10),sam_cus varchar(10))

insert into sadf values ('00000001'   ,'20120302' ,'110')    
insert into sadf values ('00000001'   ,'20120302' ,'110')    
insert into sadf values ('00000001'   ,'20120302' ,'110')    
insert into sadf values ('00000001'   ,'20120302' ,'110')    
insert into sadf values ('00000001'   ,'20120302' ,'110')    

if OBJECT_ID('dbo.samf') is not null
    drop table dbo.samf
go

create table samf
(sad_k1 varchar(10),sad_k2 varchar(10),sad_inv varchar(20))

insert into samf values ('00000001','1','110-3318A-0709')
insert into samf values ('00000001','2','110-3318A-0711')
insert into samf values ('00000001','3','110-3318A-0713')
insert into samf values ('00000001','4','110-3318A-1009')
insert into samf values ('00000001','5','110-3318A-1011')                         

select sam_key,sam_dte,sam_cus,sad_k1,sad_k2,sad_inv
from sadf join samf on sad_k1=sam_key
order by sam_key;

 

我們可透過 ROW_NUMBER()+ PARTITION BY 的方式，來設定流水號，再將判斷如果流水號大於1就傳出空字串，如下，

SELECT  CASE WHEN RN > 1 THEN ''
             ELSE sam_key
        END AS sam_key ,
        CASE WHEN RN > 1 THEN ''
             ELSE sam_dte
        END AS sam_dte ,
        CASE WHEN RN > 1 THEN ''
             ELSE sam_cus
        END AS sam_cus ,
        CASE WHEN RN > 1 THEN ''
             ELSE sad_k1
        END AS sad_k1 ,
        *
FROM    ( SELECT    RN = ROW_NUMBER() OVER ( PARTITION BY sam_key, sam_dte,
                                             sam_cus, sad_k1 ORDER BY sam_key ) ,
                    sam_key ,
                    sam_dte ,
                    sam_cus ,
                    sad_k1 ,
                    sad_k2 ,
                    sad_inv
          FROM      sadf
                    JOIN samf ON sad_k1 = sam_key
        ) T1

 

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^