sql中查询出的重复字段只显示一条

[wozaiztam]

[img=https://img-bbs.csdn.net/upload/201506/23/1435029303_151177.png]q[/img]


如图所示，我只 希望这些重复的零售店编码出现一条，其他的为空格，，

sql:

SELECT co.PartnerCode as 零售店编码,ol.CouponCode as 券号,
(case ol.Status when 'Unused' then '未使用' when 'K1Confirmed' then 'K1已确认' when 'BeenUsed' then '已使用'  end) as 券状态,
CONVERT(varchar(100), ol.RecordCreatedDate, 20) as 兑换时间
FROM GitiCustomer.CouponOrder CO
join GitiCustomer.CouponOrderLine ol on CO.CouponOrderId=ol.CouponOrderId

[wozaiztam]

引用 2 楼 guangguang2007 的回复:

SELECT co.PartnerCode as 零售店编码,max(ol.CouponCode) as 券号,
(case ol.Status when 'Unused' then '未使用' when 'K1Confirmed' then 'K1已确认' when 'BeenUsed' then '已使用'  end) as 券状态,
CONVERT(varchar(100), ol.RecordCreatedDate, 20) as 兑换时间
FROM GitiCustomer.CouponOrder CO
join GitiCustomer.CouponOrderLine ol on CO.CouponOrderId=ol.CouponOrderId
group by co.PartnerCode,(case ol.Status when 'Unused' then '未使用' when 'K1Confirmed' then 'K1已确认' when 'BeenUsed' then '已使用'  end),CONVERT(varchar(100), ol.RecordCreatedDate, 20)

加个max应该可以吧~

不行哦，这样我看了下就是过滤了下重复行，但是我只希望零售店编码中有重复的为空而已

[Tiger_Zhao]

那是前台表格合并相同单元的功能。
不要在查询中做，都换成空白后怎么知道第2条是关联第1条、第4条是关联第3条的？再做其它处理（比如最简单的排序）就不方便了。

[wozaiztam]

[ggsysy]

SELECT co.PartnerCode as 零售店编码,max(ol.CouponCode) as 券号,
(case ol.Status when 'Unused' then '未使用' when 'K1Confirmed' then 'K1已确认' when 'BeenUsed' then '已使用'  end) as 券状态,
CONVERT(varchar(100), ol.RecordCreatedDate, 20) as 兑换时间
FROM GitiCustomer.CouponOrder CO
join GitiCustomer.CouponOrderLine ol on CO.CouponOrderId=ol.CouponOrderId
group by co.PartnerCode,(case ol.Status when 'Unused' then '未使用' when 'K1Confirmed' then 'K1已确认' when 'BeenUsed' then '已使用'  end),CONVERT(varchar(100), ol.RecordCreatedDate, 20)

加个max应该可以吧~

[Pact_Alice]

select 零售店编码 , case when ID=1 then 券号 else NULL end as 券号, case when ID=1 then 券状态 else NULL end as 券状态, case when ID=1 then 兑换时间 else NULL end as 兑换时间 from ( SELECT co.PartnerCode as 零售店编码, ol.CouponCode as 券号, (case ol.Status when 'Unused' then '未使用' when 'K1Confirmed' then 'K1已确认' when 'BeenUsed' then '已使用' end) as 券状态, CONVERT(varchar(100), ol.RecordCreatedDate, 20) as 兑换时间, row_number()over(partition by co.PartnerCode order by co.PartnerCode)as ID--根据PartnerCode分组排序 FROM GitiCustomer.CouponOrder CO join GitiCustomer.CouponOrderLine ol on CO.CouponOrderId=ol.CouponOrderId )a

[Pact_Alice]

select case when ID=1 then 零售店编码 else NULL end as 零售店编码, case when ID=1 then 券号 else NULL end as 券号, case when ID=1 then 券状态 else NULL end as 券状态, case when ID=1 then 兑换时间 else NULL end as 兑换时间, 零售店编码+convert(varchar(10),ID)--可以用于区分是零售店编码等全部是空时候的标记 from ( SELECT co.PartnerCode as 零售店编码, ol.CouponCode as 券号, (case ol.Status when 'Unused' then '未使用' when 'K1Confirmed' then 'K1已确认' when 'BeenUsed' then '已使用' end) as 券状态, CONVERT(varchar(100), ol.RecordCreatedDate, 20) as 兑换时间, row_number()over(partition by co.PartnerCode order by co.PartnerCode)as ID--根据PartnerCode分组排序 FROM GitiCustomer.CouponOrder CO join GitiCustomer.CouponOrderLine ol on CO.CouponOrderId=ol.CouponOrderId )a

[reenjie]

試一下下面的sql

SELECT 
co.PartnerCode as 零售店编码,
ol.CouponCode as 券号,
(case ol.Status 
when 'Unused' then '未使用' 
when 'K1Confirmed' then 'K1已确认' 
when 'BeenUsed' then '已使用'  end) as 券状态,
CONVERT(varchar(100), ol.RecordCreatedDate, 20) as 兑换时间
into #temp
FROM GitiCustomer.CouponOrder CO
join GitiCustomer.CouponOrderLine ol 
on CO.CouponOrderId=ol.CouponOrderId

select *,row_number() over(partition by 零售店编码 order by 兑换时间) as RN into #t1 from #temp 

update a set 零售店编码='' from #temp  as a,#t1 as b where b.RN>1 and a.券号=b.券号

select * from #temp