SQL查询重复字段

1.找出20171130号的new_name数，重复的不重复计算

select min(nse.new_name) from new_services ns WITH (NOLOCK)

inner join new_servicesExtensionBase  nse  WITH (NOLOCK)
on ns.new_servicesId = nse.new_servicesId
inner join new_servicesBase ns2 WITH (NOLOCK)
on ns.new_servicesId = ns2.new_servicesId
left join new_faultinfo nf  WITH (NOLOCK) 
on nse.new_servicesId = nf.new_servicesid
where 1=1
AND  '20171130'<= CONVERT(varchar(32), DATEADD(HOUR, 8, ns.CreatedOn), 112)
            AND '20171130' >=CONVERT(varchar(32), DATEADD(HOUR, 8, ns.CreatedOn), 112)

group by nse.new_name

2.找出new_name字段重复的部分

select nse.new_name from new_services ns WITH (NOLOCK)
inner join new_servicesExtensionBase  nse  WITH (NOLOCK)
on ns.new_servicesId = nse.new_servicesId
inner join new_servicesBase ns2 WITH (NOLOCK)
on ns.new_servicesId = ns2.new_servicesId
left join new_faultinfo nf  WITH (NOLOCK) 
on nse.new_servicesId = nf.new_servicesid
where 1=1
AND  '20171130'<= CONVERT(varchar(32), DATEADD(HOUR, 8, ns.CreatedOn), 112)
            AND '20171130' >=CONVERT(varchar(32), DATEADD(HOUR, 8, ns.CreatedOn), 112)
group by nse.new_name having COUNT(nse.new_name)>1

3.找出new_name字段不重复的部分

select nse.new_name from new_services ns WITH (NOLOCK)
inner join new_servicesExtensionBase  nse  WITH (NOLOCK)
on ns.new_servicesId = nse.new_servicesId
inner join new_servicesBase ns2 WITH (NOLOCK)
on ns.new_servicesId = ns2.new_servicesId
left join new_faultinfo nf  WITH (NOLOCK) 
on nse.new_servicesId = nf.new_servicesid
where 1=1
AND  '20171130'<= CONVERT(varchar(32), DATEADD(HOUR, 8, ns.CreatedOn), 112)
            AND '20171130' >=CONVERT(varchar(32), DATEADD(HOUR, 8, ns.CreatedOn), 112)
group by nse.new_name having COUNT(nse.new_name)=1