选择行与多个状态记录项不匹配的位置

Question

我有下面的表结构，我需要帮助查询

ID   Disp  Type   State    CreateDate
1     I5   Det     OH    1/1/2018 08:42:00
1     I5   Det     CO    1/1/2018 08:43:00
1     I5   Det     NC    1/2/2018 05:16:00
1     I5   ReDet   OH    1/3/2018 14:02:00
1     I5   ReDet   CO    1/3/2018 14:03:00
1     I5   ReDet   NC    1/3/2018 14:04:00
2     I5   Det     FL    1/4/2018 16:07:00
2     I5   ReDet   FL    1/4/2018 16:07:00
3     I5   Det     FL    1/4/2018 10:05:02
3     I5   ReDet   CA    1/4/2018 10:05:03
3     I5   ReDet   FL    1/5/2018 09:17:00
3     I5   ReDet   CA    1/6/2018 12:04:00
4     I5   Det     PA    1/7/2018 10:27:00
4     I5   Det     NE    1/8/2018 10:14:00
4     I5   Det     PA    1/8/2018 13:43:00
4     I5   ReDet   NE    1/9/2018 09:01:00

我想要的是：1.对于具有多个状态的每个ID，"类型"应与输入状态的顺序相匹配。例如：

ID 1:
OH -> Det 1st entry(1/1/2018) matches
CO -> Det 1st entry(1/1/2018) and
NC -> Det 1st entry(1/2/2018) 
And 
OH -> ReDet 2st entry(3/1/2018) matches
CO -> ReDet 2st entry(3/1/2018) and
NC -> ReDet 2st entry(3/1/2018) 

ID 2没有多个状态

ID 3-每个州类型的第一个条目不匹配：

FL -> Det 1st entry's (1/4/2018) type does not match
CA -> ReDet 1st entry(1/5/2018)

ID 4-每个州类型的第2个条目不匹配：

PA -> Det   1st entry(1/7/2018) type matches
NE -> Det 1st entry(1/8/2018)

但是

PA -> Det   1st entry(1/8/2018) type does not match
NE -> ReDet 1st entry(1/9/2018)

我想要一个ID有多个状态的记录，第一个、第二个、第三个，。。。每个状态的记录类型与不匹配

根据该表，输出ID为3和4。

我有以下作为开始查询：

SELECT * FROM dbo.tableLog AS log1
JOIN dbo.tableLog AS log2 ON log2.ID = log1.ID AND log2.state = log1.state
WHERE log1.type <> log2.type 

但我想我离我想要的还差得很远。

如有任何帮助，我们将不胜感激。

Answer 1

Select * into #tableLog
from
(             
Select 1 AS ID ,    'I5' as Disp,   'Det' as Type,     'OH' as State ,   '1/1/2018' 
as DteTime
union
Select 1 ,    'I5',   'Det',     'CO',    '1/1/2018'
union
Select 1 ,    'I5',   'Det',     'NC',    '1/2/2018'
union
Select 1 ,    'I5',   'ReDet',   'OH',    '1/3/2018'
union
Select 1 ,    'I5',   'ReDet',   'CO',    '1/3/2018'
union
Select 1 ,    'I5',   'ReDet',   'NC',    '1/3/2018'
union
Select 2 ,    'I5',   'Det',     'FL',    '1/4/2018'
union
Select 2 ,    'I5',   'ReDet',   'FL',    '1/4/2018'
union
Select 3 ,    'I5',   'Det',     'FL',    '1/4/2018'
union
Select 3 ,    'I5',   'ReDet',   'CA',    '1/4/2018'
union
Select 3 ,    'I5',   'ReDet',   'FL',    '1/5/2018'
union
Select 3 ,    'I5',   'ReDet',   'CA',    '1/6/2018'
union
Select 4 ,    'I5',   'Det',     'PA',    '1/7/2018'
union
Select 4 ,    'I5',   'Det',     'NE',    '1/8/2018'
union
Select 4 ,    'I5',   'Det',     'PA',    '1/8/2018'
union
Select 4 ,    'I5',   'ReDet',   'NE',    '1/9/2018'
)t

Select a.rowno as row1 ,a.Type, b.rowno as row2 ,b.Type ,a.Disp,  a.State ,a. 
[DteTime] as Datetime1,b.[DteTime] as Datetime2 from 
(
Select ROW_NUMBER()over (partition by ID,Type order by [DteTime],State asc ) rowno, 
* from #tableLog t1
) a 
inner join
(
Select ROW_NUMBER()over (partition by ID,Type order by [DteTime] asc) rowno, * from 
#tableLog t2
) b on a.id = b.id and a.disp = b.disp and a.State = b.State 
where a.Type ='Det' and b.type = 'ReDet'
--and a.rowno <> b.rowno 
order by a.ID,a.rowno 
drop table #tablelog

Answer 2

Select * into #tableLog
from
(             
Select 1 AS ID ,    'I5' as Disp,   'Det' as Type,     'OH' as State ,   '1/1/2018 
08:42:00' 
as DteTime
union
Select 1 ,    'I5',   'Det',     'CO',    '1/1/2018 08:43:00'
union
Select 1 ,    'I5',   'Det',     'NC',    '1/2/2018  05:16:00'
union
Select 1 ,    'I5',   'ReDet',   'OH',    '1/3/2018 14:02:00'
union
Select 1 ,    'I5',   'ReDet',   'CO',    '1/3/2018 14:03:00'
union
Select 1 ,    'I5',   'ReDet',   'NC',    '1/3/2018 14:04:00'
union
Select 2 ,    'I5',   'Det',     'FL',    '1/4/2018 16:07:00'
union
Select 2 ,    'I5',   'ReDet',   'FL',    '1/4/2018 16:07:00'
union
Select 3 ,    'I5',   'Det',     'FL',    '1/4/2018 10:05:02'
union
Select 3 ,    'I5',   'ReDet',   'CA',    '1/4/2018 10:05:03'
union
Select 3 ,    'I5',   'ReDet',   'FL',    '1/5/2018 09:17:00'
union
Select 3 ,    'I5',   'ReDet',   'CA',    '1/6/2018 12:04:00'
union
Select 4 ,    'I5',   'Det',     'PA',    '1/7/2018 10:27:00'
union
Select 4 ,    'I5',   'Det',     'NE',    '1/8/2018 10:14:00'
union
Select 4 ,    'I5',   'Det',     'PA',    '1/8/2018 13:43:00'
union
Select 4 ,    'I5',   'ReDet',   'NE',    '1/9/2018 09:01:00'
)t


Select a.ID, a.rowno as row1 ,a.Type, b.rowno as row2 ,b.Type ,a.Disp,  a.State ,a. 
[DteTime] as Datetime1,b.[DteTime] as Datetime2 from 
(
Select ROW_NUMBER()over (partition by ID,Type order by [DteTime] asc ) 
rowno, 
* from #tableLog t1
) a 
inner join
(
Select ROW_NUMBER()over (partition by ID,Type order by [DteTime] asc) rowno, * 
from 
#tableLog t2
) b on a.id = b.id and a.disp = b.disp and a.State = b.State 
where a.Type ='Det' and b.type = 'ReDet'
and a.rowno <> b.rowno 
order by a.ID,a.rowno 
drop table #tablelog