求一相邻数更新的SQL 请高手大神指点 在线等

[weixin_42068029]

SELECT * INTO A_Table_Z FROM 
(
 SELECT 1 AS TID,'1001' AS PIID,'项目1' AS PIName,'2' AS AreaID,'市内' AS AreaName, '2018-01-01' AS LTDate 
   UNION ALL
 SELECT 2 AS TID,'1002' AS PIID,'项目2' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-02' AS LTDate 
    UNION ALL
 SELECT 13 AS TID,'1005' AS PIID,'项目5' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-07' AS LTDate 
   UNION ALL
 SELECT 18 AS TID,'1001' AS PIID,'项目1' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-13' AS LTDate 
   UNION ALL
 SELECT 20 AS TID,'1002' AS PIID,'项目2' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-14' AS LTDate  
   UNION ALL
 SELECT 22 AS TID,'1003' AS PIID,'项目3' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-15' AS LTDate  
) AS A

-- 原始数据是这样
TID    PIID    PIName    AreaID    AreaName    LTDate
1    1001    项目1    2    市内    2018-01-01
2    1002    项目2    2    市内    2018-01-02
13    1005    项目5    2    市内    2018-01-07
18    1001    项目1    2    市内    2018-01-13
20    1002    项目2    2    市内    2018-01-14
22    1003    项目3    2    市内    2018-01-15
 
-- 更改为 这样的数据
TID    PIID    PIName    AreaID    AreaName    LTDate
1    1001    项目1    2    市内    2018-01-01
2    1001    项目1    2    市内    2018-01-02
13    1005    项目5    2    市内    2018-01-07
18    1001    项目1    2    市内    2018-01-13
20    1001    项目1    2    市内    2018-01-14
22    1001    项目1    2    市内    2018-01-15
-- --就是将连续俩天或以上的数据 PIID都改为连续天数开头的第一天的PIID。

--我的脚本
UPDATE A_Table_A SET PIID=A.PIID,PIName=A.PIName
FROM (SELECT PIID,PIName,LTDate FROM A_Table_A) AS A
WHERE DATEADD(DAY,1,A_Table_A.LTDate)=A.LTDate

可以写这样只能更新连续两天的数据 两天以上的就不更新了 请高手指点

[weixin_42068029]

请高手指点 拜托拜托

[RINK_1]

 
 with cte_1
 as
 (select *,ROW_NUMBER() over (order by LTDate) as seq_1,DATEDIFF(DAY,'1900-01-01',LTDate) as seq_2 from A_Table_Z),
 
cte_2
as
( select *,seq_2-seq_1 as group_char from cte_1) 

update A
set PIID=B.PIID,
    PIName=B.PIName
from cte_2 A
outer apply (select top 1 PIID,PIName from cte_2 where A.group_char=group_char order by LTDate) as B
 

[听雨停了]

SELECT * INTO A_Table_Z FROM 
(
 SELECT 1 AS TID,'1001' AS PIID,'项目1' AS PIName,'2' AS AreaID,'市内' AS AreaName, '2018-01-01' AS LTDate 
   UNION ALL
 SELECT 2 AS TID,'1002' AS PIID,'项目2' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-02' AS LTDate 
    UNION ALL
 SELECT 13 AS TID,'1005' AS PIID,'项目5' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-07' AS LTDate 
   UNION ALL
 SELECT 18 AS TID,'1001' AS PIID,'项目1' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-13' AS LTDate 
   UNION ALL
 SELECT 20 AS TID,'1002' AS PIID,'项目2' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-14' AS LTDate  
   UNION ALL
 SELECT 22 AS TID,'1003' AS PIID,'项目3' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-15' AS LTDate  
) AS A
--测试数据结束


;WITH cte AS (
SELECT *,ROW_NUMBER() OVER(ORDER BY LTDate)-DATEDIFF(DAY,'1900-01-01',LTDate) AS gp 
FROM A_Table_Z
),cte2 AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY gp ORDER BY ltdate) AS rn FROM cte
)
UPDATE a SET a.piid=b.piid,a.piname=b.piname
FROM cte as a,cte2 AS b
WHERE a.gp=b.gp AND b.rn=1

SELECT  * FROM A_Table_Z


TID         PIID PIName AreaID AreaName LTDate
----------- ---- ------ ------ -------- ----------
1           1001 项目1    2      市内       2018-01-01
2           1001 项目1    2      市内       2018-01-02
13          1005 项目5    2      市内       2018-01-07
18          1001 项目1    2      市内       2018-01-13
20          1001 项目1    2      市内       2018-01-14
22          1001 项目1    2      市内       2018-01-15