表C结构如下
Company nvarchar(2) --公司 主健
Agencyid nvarchar(10) --工号 主健
Policy nvarchar(8) --保单号 主健
Crtable nvarchar(4) –险种代码 主健
Life nvarhar(2)
Coverage nvarchar(2)
Rider nvarchar(2)
要求编写SQL语句查询(companycode,policycode,crtable)组合键值重复的记录明细。
Company nvarchar(2) --公司 主健
Agencyid nvarchar(10) --工号 主健
Policy nvarchar(8) --保单号 主健
Crtable nvarchar(4) –险种代码 主健
Life nvarhar(2)
Coverage nvarchar(2)
Rider nvarchar(2)
要求编写SQL语句查询(companycode,policycode,crtable)组合键值重复的记录明细。
from tc c
where exists(select 1 from tc where companycode = c.companycode and policycode = c.policycode and crtable = c.crtable and agencyid != c.agencyid)
(
select 1 from (select Company , Policy , Crtable from c group by Company , Policy , Crtable having count(1) > 1) m
where Company = t.Company and Policy and t.Policy and Crtable and t.Crtable
)
order by t.Company , t.Policy , t.Crtable
*
from
c t
where
exists(select 1 from c where companycode = t.companycode and policycode = t.policycode and crtable = t.crtable and agencyid <> t.agencyid)
where (select count(*) from c where company=a.company and policy=a.policy and crtable=a.crtable)>1
order by company,policy,crtable