34,576
社区成员
发帖
与我相关
我的任务
分享
declare @t table(LOTID varchar(10),TESTCNT int,POWER int)
insert @t
select '0001','1',370
union all select '0001','1',365
union all select '0001','2',370
union all select '0001','3',370
union all select '0002','1',365
union all select '0003','1',380
union all select '0004','1',390
union all select '0004','2',395
union all select '0005','1',NULL
union all select '0005','2',370
union all select '0006','1',NULL
union all select '0007','1',370
union all select '0007','2',NULL
union all select '0007','3',375
select * from @t t1
where exists
(select 1
from
(select top 1 * from @t t3 where t1.LOTID =t3.LOTID order by t3.TESTCNT desc
)t2
where t1.LOTID =t2.lotid
and t1.TESTCNT+1=t2.TESTCNT
and t1.POWER !=t2.POWER
)
declare @t table(LOTID varchar(10),TESTCNT int,POWER int)
insert @t
select '0001','1',370
union all select '0001','1',365
union all select '0001','2',370
union all select '0001','3',370
union all select '0002','1',365
union all select '0003','1',380
union all select '0004','1',390
union all select '0004','2',395
union all select '0005','1',NULL
union all select '0005','2',370
union all select '0006','1',NULL
union all select '0007','1',370
union all select '0007','2',NULL
union all select '0007','3',375
select * from @t t1
where exists
(select 1 from @t t2
where t1.LOTID =t2.lotid
and t1.POWER !=(select top 1 t3.POWER from @t t3 where t2.LOTID =t3.LOTID order by t3.TESTCNT desc)
group by t2.lotid
having t1.TESTCNT+1=max(t2.TESTCNT)
)
declare @t table(LOTID varchar(10),TESTCNT int,POWER int)
insert @t
select '0001','1',370
union all select '0001','1',365
union all select '0001','2',370
union all select '0001','3',370
union all select '0002','1',365
union all select '0003','1',380
union all select '0004','1',390
union all select '0004','2',395
union all select '0005','1',NULL
union all select '0005','2',370
union all select '0006','1',NULL
union all select '0007','1',370
union all select '0007','2',NULL
union all select '0007','3',375
select lotid from @t t1
where exists
(select 1 from @t t2
where t1.LOTID =t2.lotid
and t1.POWER !=t2.POWER
group by t2.lotid
having t1.TESTCNT+1=max(t2.TESTCNT)
)
/*
lotid
0004
*/
declare @t table(lotid varchar(5),testcnt int,power int)
insert into @t(lotid,testcnt,power) values('0001',1,370),('0001',2,365),('0001',3,370),('0002',1,365),('0002',2,365),('0003',1,380),('0004',1,390),('0004',2,395),('0005',1,null),('0005',2,370),('0006',1,null),('0007',1,370),('0007',2,null),('0007',3,375),('0008',1,370),('0008',2,375),('0008',3,375)
select lotid from @t a where not exists(select 1 from @t where lotid=a.lotid and testcnt>a.testcnt) and exists(select 1 from @t where lotid=a.lotid and testcnt=a.testcnt-1 and power<>a.power)
不用row_number和group来判断是否最后一次测试declare @t table(lotid varchar(5),testcnt int,power int)
insert into @t(lotid,testcnt,power) values('0001',1,370),('0001',2,365),('0001',3,370),('0002',1,365),('0002',2,365),('0003',1,380),('0004',1,390),('0004',2,395),('0005',1,null),('0005',2,370),('0006',1,null),('0007',1,370),('0007',2,null),('0007',3,375),('0008',1,370),('0008',2,375),('0008',3,375)
select lotid from (select *,row_number() over(partition by lotid order by testcnt desc) as rowid from @t) a where rowid=1 and exists(select 1 from @t where lotid=a.lotid and testcnt=a.testcnt-1 and power<>a.power)
declare @t table(LOTID varchar(10),TESTCNT int,POWER int)
insert @t
select '0001','1',370
union all select '0001','2',365
union all select '0001','3',370
union all select '0002','1',365
union all select '0002','2',365
union all select '0003','1',380
union all select '0004','1',390
union all select '0004','2',395
union all select '0005','1',NULL
union all select '0005','2',370
union all select '0006','1',NULL
union all select '0007','1',370
union all select '0007','2',NULL
union all select '0007','3',375
select distinct lotid from @t t1
where exists
(select 1 from @t t2
where t1.LOTID =t2.lotid
and t1.TESTCNT =t2.testcnt+1
and t1.POWER !=t2.power
)
/*
lotid
0001
0004
*/
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([LOTID] nvarchar(24),[TESTCNT] int,[POWER] int)
Insert #T
select N'0001',1,365 union all
select N'0001',2,365 union all
select N'0001',3,370 union all
select N'0002',1,365 union all
select N'0002',2,365 union all
select N'0003',1,380 union all
select N'0004',1,390 union all
select N'0004',2,395 union all
select N'0005',1,null union all
select N'0005',2,370 union all
select N'0006',1,null
Go
--测试数据结束
;WITH ctea AS (
SELECT LOTID,MAX(TESTCNT) AS TESTCNT FROM #T GROUP BY LOTID HAVING MAX(TESTCNT)>1
)
SELECT ctea.LOTID
FROM ctea
JOIN #T a ON a.LOTID = ctea.LOTID
AND a.TESTCNT = ctea.TESTCNT
JOIN #T b ON b.LOTID = ctea.LOTID
AND b.TESTCNT = ctea.TESTCNT - 1
WHERE a.POWER <> b.POWER
declare @t table(lotid varchar(5),testcnt int,power int)
insert into @t(lotid,testcnt,power) values('0001',1,370),('0001',2,365),('0001',3,370),('0002',1,365),('0002',2,365),('0003',1,380),('0004',1,390),('0004',2,395),('0005',1,null),('0005',2,370),('0006',1,null),('0007',1,370),('0007',2,null),('0007',3,375)
select lotid from @t a where exists(select top 1 1 from @t where lotid=a.lotid and testcnt<>a.testcnt and power<>a.power) and not exists(select top 1 1 from @t where lotid=a.lotid and power is null) group by lotid