时间戳

首页 > 解决方案 > 如何在不使用循环的情况下更新 SQL Server 中两组不同条件之间的行

sql-server - 如何在不使用循环的情况下更新 SQL Server 中两组不同条件之间的行

问题描述

问题：如何在不使用循环的情况下更新 SQL Server 中两组不同条件之间的行 (SQL Server 2014)。换句话说，对于结果集中的每一行，如何更新第一次出现（使用一个标准）和第二次出现（使用不同的标准）之间的每一行。我认为部分问题是尝试为查询中的每一行运行 TOP N 查询。

具体来说：在下面的示例起始表中，如何更新日期的最后两列：

  1. 如果空类别行前面有一个“S”类别，则更新空类别行和最后一个连续的“M”类别行之间的行。类别可以包含“S”、“M”或 null 的任意顺序。

  2. 设置空行之前的“S”行的 StartDate = IDEndDate+1 天。

  3. 将最后一行的 EndDate = IDEndDate 设置为“M”类别。

这是一个SQLFiddle。

注意：我过去曾使用循环（获取..）来完成此操作，但我试图通过一些查询来执行此操作，而不是类似：

第 1 步：开始工作：选择所有有效的空行（范围的开头）

第 2 步：对于上面的每一行，选择相关的最后一个“M”行（范围结束），然后运行查询以更新每个范围中的 StartDate、EndDates。

Starting Table:
ID  IDStartDate IDEndDate   Category
------------------------------------
11  2017-01-01  2017-01-31  S
11  2017-02-02  2017-02-03  null
11  2017-02-03  2017-03-31  M
11  2017-04-01  2017-04-30  M
22  2017-05-01  2017-06-15  S
22  2017-06-16  2017-06-20  null
22  2017-06-21  2017-06-25  M
22  2017-06-26  2017-06-27  null
22  2017-06-28  2017-06-29  S
22  2017-06-30  2017-07-05  M
33  2017-06-30  2017-07-14  M
33  2017-07-15  2017-07-20  S
33  2017-07-21  2017-07-25  null
44  2018-06-30  2018-07-14  S
44  2018-07-15  2018-07-20  M
44  2018-07-21  2018-07-25  null


Desired Ending Table:
ID  IDStartDate IDEndDate  Category StartDate   EndDate 
----------------------------------------------------------
11  2017-01-01  2017-01-31 S        
11  2017-02-02  2017-02-03 null     2017-02-01  2017-04-30  
11  2017-02-03  2017-03-31 M        2017-02-01  2017-04-30
11  2017-04-01  2017-04-30 M        2017-02-01  2017-04-30
22  2017-05-01  2017-06-15 S        
22  2017-06-16  2017-06-20 null     2017-06-16  2017-06-25  
22  2017-06-21  2017-06-25 M        2017-06-16  2017-06-25
22  2017-06-26  2017-06-27 null
22  2017-06-28  2017-06-29 S
22  2017-06-30  2017-07-05 M
33  2017-06-30  2017-07-14 M
33  2017-07-15  2017-07-20 S
33  2017-07-21  2017-07-25 null
44  2018-06-30  2018-07-14 S
44  2018-07-15  2018-07-20 M
44  2018-07-21  2018-07-25 null

下面是一些用于创建表和查看我已经启动的查询结果的 SQL。我尝试了 cte、交叉应用、外部应用、内部连接......没有运气。非常感谢！

CREATE TABLE test (
    ID INT,
    IDStartDate date,
    IDEndDate date,
    Category VARCHAR (2),
    StartDate date,
    EndDate date
);
INSERT INTO test (ID, IDStartDate, IDEndDate, Category)
VALUES 
 (11, '2017-01-01', '2017-01-31', 'S')
,(11, '2017-02-02', '2017-02-03', null) 
,(11, '2017-02-03', '2017-03-31', 'M') 
,(11, '2017-04-01', '2017-04-30', 'M') 
,(22, '2017-05-01', '2017-06-15', 'S')
,(22, '2017-06-16', '2017-06-20', null)
,(22, '2017-06-21', '2017-06-25', 'M')
,(22, '2017-06-26', '2017-06-27', null)
,(22, '2017-06-28', '2017-06-29', 'S')
,(22, '2017-06-30', '2017-07-05', 'M')
,(33, '2017-06-30', '2017-07-14', 'M')
,(33, '2017-07-15', '2017-07-20', 'S')
,(33, '2017-07-21', '2017-07-25', null)
,(44, '2018-06-30', '2018-07-14', 'S')
,(44, '2018-07-15', '2018-07-20', 'M')
,(44, '2018-07-21', '2018-07-25', null);


--**************************
--results: shows first rows of each range
--**************************
;with cte as
(
select *
,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID, IDStartDate, IDEndDate) AS RowNum
,LAG(IDEndDate) OVER(PARTITION BY ID ORDER BY ID, IDStartDate, IDEndDate) AS lastIDEndDate
,LAG(Category) OVER(PARTITION BY ID ORDER BY ID, IDStartDate, IDEndDate) AS lastCategory
,LEAD(Category) OVER(PARTITION BY ID ORDER BY ID, IDStartDate, IDEndDate) AS nextCategory
from test
)
select *  --select first row of each range to update
from cte
where Category is null and lastCategory = 'S' and nextCategory = 'M'


--*******************************
--6 of 8 "new" values are correct (missing NewEndDate for first range)
--*******************************
;with cte as
(
SELECT *
,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID, IDStartDate, IDEndDate) AS RowNum
,LAG(IDEndDate) OVER(PARTITION BY ID ORDER BY ID, IDStartDate, IDEndDate) AS lastIDEndDate
,LAG(Category) OVER(PARTITION BY ID ORDER BY ID, IDStartDate, IDEndDate) AS lastCategory
,LEAD(Category) OVER(PARTITION BY ID ORDER BY ID, IDStartDate, IDEndDate) AS nextCategory
FROM test
), cte2 as
(
select *        --find the first/start row of each range
,LAG(RowNum) OVER(PARTITION BY ID ORDER BY ID, IDStartDate, IDEndDate) AS lastRowNum
,IIF(Category is null and lastCategory = 'S' and nextCategory = 'M', DateAdd(day, 1, lastIDEndDate), null) as NewStartDate
,IIF(Category is null and lastCategory = 'S' and nextCategory = 'M', RowNum, null) as NewStartRowNum
from cte
)
select t1.*, t3.*
from cte2 t1
outer apply
(       
  select top 1   --find the last/ending row of each range
   t2.lastIDEndDate as NewEndDate  
  ,t2.lastRowNum as NewEndRowNum
  from cte2 t2
  where t1.ID = t2.ID
  and t1.NewStartRowNum < t2.RowNum
  and t2.nextCategory <> 'M'  
  order by t2.ID, t2.RowNum
) t3
order by t1.ID, t1.RowNum

标签： sql-serverloopssql-updaterangebetween

解决方案

这是对这个 SQL 谜题的尝试。

基本上，它从 CTE 更新。

首先，它计算累积和。创建某种排名。

然后仅对于排名 2 和 3，它会计算日期。 

;WITH CTE AS
(
    SELECT ID, IDStartDate, IDEndDate, Category, StartDate, EndDate,
    DATEADD(day,1, FIRST_VALUE(IDEndDate) OVER (PARTITION BY ID ORDER BY IDStartDate)) AS NewStartDate,
      FIRST_VALUE(IDEndDate) OVER (PARTITION BY ID ORDER BY IDStartDate DESC) AS NewEndDate
    FROM
    (
        SELECT ID, IDStartDate, IDEndDate, Category, StartDate, EndDate,
        SUM(CASE WHEN Category = 'S' THEN 2 WHEN Category IS NULL THEN 1 END) OVER (PARTITION BY ID ORDER BY IDStartDate) AS cSum
        FROM test t
    ) q
    WHERE cSum IN (2, 3)
)
UPDATE CTE
SET
    StartDate = NewStartDate, 
    EndDate = NewEndDate
WHERE (Category IS NULL OR Category = 'M');

对 reextester 的测试在这里

推荐阅读