我试图根据状态代码查找表中的间隙，表如下所示。
状态表：
StateID (PK) | Code
--------------------
1 | AK
2 | AL
3 | AR
状态模型表：
StateModelID | StateID | EfftiveDate | ExpirationDate
-------------------------------------------------------------------------
1 | 1 | 2012-06-28 00:00:00.000| 2012-08-02 23:59:59.000
2 | 1 | 2012-08-03 00:00:00.000| 2050-12-31 23:59:59.000
3 | 1 | 2055-01-01 00:00:00.000| 2075-12-31 23:59:59.000
我正在使用的查询如下：
Declare @gapMessage varchar(250)
SET @gapMessage = ''
select
@gapMessage = @gapMessage +
(Select StateTable.Code FROM StateTable where t1.StateID = StateTable.StateID)
+ ' Row ' +CAST(t1.StateModelID as varchar(6))+' has a gap with '+
CAST(t2.StateModelID as varchar(6))+ CHAR(10)
from StateModel t1
inner join StateModel t2
on
t1.StateID = t2.StateID
and DATEADD(ss, 1,t1.ExpirationDate) < t2.EffectiveDate
and t1.EffectiveDate < t2.EffectiveDate
if(@gapMessage != '')
begin
Print 'States with a gap problem'
PRINT @gapMessage
end
else
begin
PRINT 'No States with a gap problem'
end
但通过上表示例，我得到以下输出：
States with a gap problem
AK Row 1 has a gap with 3
AK Row 2 has a gap with 3
是否有办法重构我的查询，使 1 和 3 之间的间隙不显示，因为 1 和 2 之间没有间隙？
我正在使用 MS sql server 2008
谢谢
WITH
sequenced AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY StateID ORDER BY EfftiveDate) AS SequenceID,
*
FROM
StateModel
)
SELECT
*
FROM
sequenced AS a
INNER JOIN
sequenced AS b
ON a.StateID = b.StateID
AND a.SequenceID = b.SequenceID - 1
WHERE
a.ExpirationDate < DATEADD(second, -1, b.EfftiveDate)
为了使其尽可能有效，还要添加一个索引(StateID, EfftiveDate)
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
