sql server：比较组中的行值并更新同一表中的字段

eulz3vhy 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(315)

我想更新2列 Enr 以及 Drop 基于 StartDate 以及 EndDate 对于一套 ID 价值观。
在所附的屏幕截图中，例如 ID = 82 ，我们有2行，所以必须比较2行。第一行为结束日期（2013年12月13日），下一行为开始日期（2014年2月17日），以及 if datediff(dy, Startdate, Enddate) > 30 我必须更新这些列 Drop 从第1行到第n行 Enr 从第2行到第y行。
对组中所有匹配的id执行相同的比较。我想迭代每一组id值，比较开始和结束日期，并在此基础上更新err和drop列。

请提供最好的方式写这个查询输入？非常感谢您的帮助。
谢谢，斯维莎

sql sql-server

来源：https://stackoverflow.com/questions/62219287/sql-server-compare-row-values-in-a-group-and-update-a-field-in-the-same-table

2条答案

按热度按时间

brqmpdu11#

尝试使用 CTE 以及 OUTER APPLY 组合如下：

DECLARE @table TABLE(id INT, StartDate DATE, EndDate DATE, Enr CHAR(1), [Drop] CHAR(1))
INSERT INTO @table VALUES
(82,'2010-12-14','2013-12-13','Y','N'),
(82,'2014-02-17','2016-12-21','N','Y'),
(125,'2010-12-22','2015-06-23','Y','N'),
(125,'2015-06-23','2015-06-30','N','N'),
(125,'2015-08-16',NULL,'N','N'),
(555,'2010-12-28','2017-03-31','Y','N'),
(555,'2017-03-31',NULL,'N','N')

;WITH cte AS(
    SELECT t.*, 
        RANK() OVER(PARTITION BY id ORDER BY id, StartDate) rnk
    FROM @table t
)
UPDATE ta SET ta.[Drop] = CASE WHEN t1.Diff>30 THEN 'Y' ELSE ta.[Drop] END,
              ta.[Enr] = CASE WHEN t2.Diff>30 THEN 'Y' ELSE ta.[Enr] END
FROM cte t
INNER JOIN @table ta ON ta.id = t.id AND ta.StartDate = t.StartDate
OUTER APPLY(SELECT id, t1.StartDate, DATEDIFF(DAY,t.EndDate, t1.StartDate) Diff
            FROM cte t1
            WHERE t1.id = t.id
            AND t1.rnk = t.rnk+1) t1
OUTER APPLY(SELECT id, t.StartDate, DATEDIFF(DAY,t2.EndDate, t.StartDate) Diff
            FROM cte t2
            WHERE t2.id = t.id
            AND t2.rnk+1 = t.rnk) t2

输出：

id  StartDate   EndDate     Enr Drop
82  2010-12-14  2013-12-13  Y   Y
82  2014-02-17  2016-12-21  Y   Y
125 2010-12-22  2015-06-23  Y   N
125 2015-06-23  2015-06-30  N   Y
125 2015-08-16  NULL        Y   N
555 2010-12-28  2017-03-31  Y   N
555 2017-03-31  NULL        N   N

注：请确认 StartDate 对于ID125，这在更新前后是不同的。

赞(0）回复(0）举报 2021-07-26

xqnpmsa82#

我希望它能解决你的问题

DECLARE @table TABLE(id INT, StartDate DATE, EndDate DATE, Enr CHAR(1), [Drop] CHAR(1))
            INSERT INTO @table VALUES
            (82,'2010-12-14','2013-12-13','Y','N'),
            (82,'2014-02-17','2016-12-21','N','Y'),
            (125,'2010-12-22','2015-06-23','Y','N'),
            (125,'2015-06-23','2015-06-30','N','N'),
            (125,'2015-08-16',NULL,'N','N'),
            (555,'2010-12-28','2017-03-31','Y','N'),
            (555,'2017-03-31',NULL,'N','N')

            select * from @table

            ;WITH cte AS(
                SELECT t.*, 
                    RANK() OVER(PARTITION BY id ORDER BY id, StartDate) rnk,
                    lead(StartDate,1)OVER (PARTITION BY id ORDER BY id, StartDate) as NextStartDate,
                    Lag(enddate,1)OVER (PARTITION BY id ORDER BY id, StartDate) as PrevEndDate

                FROM @table t
            )

            --select * from(
            --select *,DATEDIFF(DD,PrevEndDate,StartDate) q,DATEDIFF(DD,EndDate,NextStartDate) r,
            --case when (DATEDIFF(DD,PrevEndDate,StartDate) >30 and [Enr]='N') or [Enr]='Y' then 'Y' else 'N' end  newEnr,
            --case when (DATEDIFF(DD,EndDate,NextStartDate) >30 and [Drop]='N') or[Drop]='Y' then 'Y' else 'N' end newdrop 
            --from cte) as a

            update t
            set Enr=case when (DATEDIFF(DD,c.PrevEndDate,c.StartDate) >30 and c.[Enr]='N') or c.[Enr]='Y' then 'Y' else 'N' end,
            t.[Drop]=  case when (DATEDIFF(DD,c.EndDate,c.NextStartDate) >30 and c.[Drop]='N') or c.[Drop]='Y' then 'Y' else 'N' end
            from @table t
            inner join cte as c on t.id=c.id and t.StartDate=c.StartDate

            select * from @table

赞(0）回复(0）举报 2021-07-26

我来回答

sql server：比较组中的行值并更新同一表中的字段

2条答案

相关问题

热门标签

最新问答