如何按顺序更新5组记录 [英] How to update records in groups of 5 sequentially

问题描述

您好，



在第一组更新后，表格中的日期字段必须以五个为一组进行更新

日期将增加1，接下来的5条记录将被更新，直到表格中的所有记录

将用尽。





我如何使用QUERY



开始日期'14 / 01/2018'来完成:: MS SQL SERVER'





IDNO NAME DATE_CHG

----------------- ---------

001 AMY 14/01/2018

002 TET 14/01/2018

003 YAL 14 / 01/2010

004 PET 14/01/2018

005 JON 14/01/2018



0011 PAM 15/01/2018

0022 SETH 15/01/2018

0033 YALE 15/01/2018

0044 ANDY 15/01 / 2018

0055 JOHN 15/01/2018





谢谢



我尝试了什么：



这是一个正在进行的开发项目早期的代码

Hello,

A date field in a table has to be updated in groups of fives after the the first group is update the
date will be incremented by 1 and the next 5 records will be updated, till the whole records in the table
will be exhausted.


How do I accomplish this in :: MS SQL SERVER using the QUERY

start date '14/01/2018'


IDNO NAME DATE_CHG
--------------------------
001 AMY 14/01/2018
002 TET 14/01/2018
003 YAL 14/01/2018
004 PET 14/01/2018
005 JON 14/01/2018

0011 PAM 15/01/2018
0022 SETH 15/01/2018
0033 YALE 15/01/2018
0044 ANDY 15/01/2018
0055 JOHN 15/01/2018


Thanks

What I have tried:

This is an ongoing development looked at earlier codes

推荐答案

这将是棘手的，因为你不能在UPDATE语句中直接使用GROUP BY（UPDATE不允许聚合函数。

但是......你可以将A JOIN用于SELECT ... GROUP BY，但即使这样，它也会很复杂，因为你需要SELECT将行ID值与你可以使用的数字一起返回在ADDDAYS函数中生成日期代码。

最后一点相对简单： ROW_NUMBER（Transact-SQL）| Microsoft Docs [ ^ ]将为您提供如果您使用模数5。

但是它会变得复杂，并且维护和修改很复杂。



我很想说这应该在演示软件中完成，而不是仅仅在SQL中，因此它更容易阅读！

It's going to be tricky, as you can't directly use GROUP BY in an UPDATE statement (UPDATE doesn't allow aggregate functions.
But... you could use A JOIN to a SELECT...GROUP BY but even then, it's going to be complicated, because you need the SELECT to return the row ID values together with a number you can use in the ADDDAYS function to generate the date code.
The last bit is relatively easy: ROW_NUMBER (Transact-SQL) | Microsoft Docs[^] will give you that if you use modulus 5 with it.
But it's going to be complicated to work out, and complicated to maintain and modify.

I'd be tempted to say this should be done in presentation software, rather than in SQL purely so it's easier to read!

这样的事情应该有效：

Something like this should work:

DECLARE @StartDate date = '20180114';

WITH cte As
(
    SELECT
        IDNO,
        (ROW_NUMBER() OVER (ORDER BY IDNO) - 1) / 5 As DaysToAdd
    FROM
        YourTable
)
UPDATE
    T
SET
    DATE_CHG = DateAdd(day, G.DaysToAdd, @StartDate)
FROM
    YourTable As T
    INNER JOIN cte As G
    ON G.IDNO = T.IDNO
;

这篇关于如何按顺序更新5组记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！