task class barcode
101 b D2010001
102 b D2010002
101 w D2010003
101 b D2010004
102 w D2010005
102 w D2010005
task = 订单号
class = 班次(白班b晚班w)
barcode = 条码以上表数据,我要找出同一个订单号相同班次出现两个以上不同条码的结果的订单号,最终找到101
101 b D2010001
102 b D2010002
101 w D2010003
101 b D2010004
102 w D2010005
102 w D2010005
task = 订单号
class = 班次(白班b晚班w)
barcode = 条码以上表数据,我要找出同一个订单号相同班次出现两个以上不同条码的结果的订单号,最终找到101
from tb A
where exists (select 1 from tb B where A.task=B.task and A.class=B.class and A.barcode<>b.barcode)
from tb
group by task,class
having count(distinct barcode)>1
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([task] int,[class] varchar(1),[barcode] varchar(8))
insert #tb
select 101,'b','D2010001' union all
select 102,'b','D2010002' union all
select 101,'w','D2010003' union all
select 101,'b','D2010004' union all
select 102,'w','D2010005' union all
select 102,'w','D2010005'select task,class,COUNT(distinct barcode) from #tb group by task,class
having COUNT(distinct barcode)>1
--task class barcode
--101 b D2010001
--102 b D2010002
--101 w D2010003
--101 b D2010004
--102 w D2010005
--102 w D2010005
use City;
go
if OBJECT_ID(N'A',N'U') is not null drop table A
go
create table A
(
--id int identity(1,1) primary key not null,
task int,
class nvarchar(5),
barcode nvarchar(20)
)
go
insert into A
select 101, 'b','D2010001' union all
select 102, 'b','D2010002' union all
select 101 ,'w','D2010003' union all
select 101 ,'b','D2010004' union all
select 102 ,'w','D2010005' union all
select 102 ,'w','D2010005'
go
with cte as
(
select task,
ROW_NUMBER()over(partition by task,class order by barcode) as RN
from (select distinct * from A)as D
)
select task from cte where RN>=2
/*
(6 行受影响)
task
-----------
101(1 行受影响)
*/