每个患者应具有唯一的randomID，并且这些randomID应该按顺序排列，所以我应该
带回（Duplicate RandomID）和（乱序RandomID）
这是我的查询
declare @valueCheck table
(RowNumber Int Identity
,PatientID varchar(25)
,RandomID varchar(25))
insert into @ValueCheck
(PatientID, RandomID)
select
PatientID
, RandomID
From dbo.CurrentData
where QuestionID= 200
order by patientid, RandomID
如果我应用此条件，我将得到（顺序为RandomID）
Select s1.PatientID, s1. RandomID as OutOFSequence
From @ValueCheck s1
Left Join @ValueCheck s2
On s1.RandomID = s2.RandomID – 1
Where s2.RandomID is Null
如果我套用，我会得到（Duplicate RandomID）
select * from @ValueCheck
where RandomID in
(Select RandomID
From @ValueCheck
Group by RandomID
Having Count(RandomID) > 1)
我想要的是，我想要一个将两种情况结合在一起的桌子
最佳答案
你为什么不做一个联合查询。
Select s1.PatientID, s1. RandomID, 'OutOFSequence'
From @ValueCheck s1
Left Join @ValueCheck s2
On s1.RandomID = s2.RandomID – 1
Where s2.RandomID is Null
union all
select PatientID, RandomID, 'Duplicate' from @ValueCheck
where RandomID in
(Select RandomID
From @ValueCheck
Group by RandomID
Having Count(RandomID) > 1)
关于mysql - 重复编号和乱序编号，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22926275/