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