主页 / 问题 / 751639
Accepted
Daniyal Lukmanov
Asked:2020-11-30 15:10:19 +0000 UTC2020-11-30 15:10:19 +0000 UTC

MS SQL 中特定步骤的间隔

  • 772

UPDATE使用MS SQL向表中插入多个值时，是否可以指定区间中的步骤？例如，我想更新表中的值，它将ID在 100 到 500 的范围内，步长为 30。或者，作为替代方案，如果没有区间，我该怎么办，所以更新以特定步骤在一定间隔内循环发生？下面我将指出一个代码示例，如果可能的话，我想在其中实现它。

UPDATE Rates
  SET
      Amount =
(
    SELECT Amount
    FROM
    (
        SELECT KK.Amount,
               KK.OperationName
        FROM
        (
            SELECT R.Col1,
                   R.RateID,
                   JJ.Type,
                   FLOOR(JJ.AmountMin + RAND() * (JJ.AmountMax + 1 - JJ.AmountMin)) AS Amount,
                   JJ.OperationName,
                   JJ.Currency
            FROM Rates R
                 LEFT JOIN
            (
                SELECT *
                FROM OriginTransacts OT
                WHERE OT.Period = 'Month'
                      AND OT.OperationName = 'Clothes Shopping'
            ) AS JJ ON R.RateID = FLOOR(RAND() * 5 + 10)
        ) AS KK
    ) AS LL
    WHERE Amount IS NOT NULL
),
      OperationName =
(
    SELECT OperationName
    FROM
    (
        SELECT KK.Amount,
               KK.OperationName
        FROM
        (
            SELECT R.Col1,
                   R.RateID,
                   JJ.Type,
                   FLOOR(JJ.AmountMin + RAND() * (JJ.AmountMax + 1 - JJ.AmountMin)) AS Amount,
                   JJ.OperationName,
                   JJ.Currency
            FROM Rates R
                 LEFT JOIN
            (
                SELECT *
                FROM OriginTransacts OT
                WHERE OT.Period = 'Month'
                      AND OT.OperationName = 'Clothes Shopping'
            ) AS JJ ON R.RateID = FLOOR(RAND() * 5 + 10)
        ) AS KK
    ) AS LL
    WHERE Amount IS NOT NULL
)
WHERE Rates.RateID IN(FLOOR(RAND() * 5 + 1110), FLOOR(RAND() * 5 + 1140), FLOOR(RAND() * 5 + 1170), FLOOR(RAND() * 5 + 1200), FLOOR(RAND() * 5 + 1230), FLOOR(RAND() * 5 + 1260), FLOOR(RAND() * 5 + 1290), FLOOR(RAND() * 5 + 1320), FLOOR(RAND() * 5 + 1350), FLOOR(RAND() * 5 + 1380), FLOOR(RAND() * 5 + 1410), FLOOR(RAND() * 5 + 1440))
      AND Rates.Amount IS NULL;
sql

2 个回答

  1. Best Answer

    到目前为止，随手：

    DECLARE @i INT= 100;

WHILE @i <= 500
    BEGIN
        UPDATE Rates
          SET
              Amount =
        (
            SELECT Amount
            FROM
            (
                SELECT KK.Amount,
                       KK.OperationName
                FROM
                (
                    SELECT R.Col1,
                           R.RateID,
                           JJ.Type,
                           FLOOR(JJ.AmountMin + RAND() * (JJ.AmountMax + 1 - JJ.AmountMin)) AS Amount,
                           JJ.OperationName,
                           JJ.Currency
                    FROM Rates R
                         LEFT JOIN
                    (
                        SELECT *
                        FROM OriginTransacts OT
                        WHERE OT.Period = 'Month'
                              AND OT.OperationName = 'Clothes Shopping'
                    ) AS JJ ON R.RateID = FLOOR(RAND() * 5 + 10)
                ) AS KK
            ) AS LL
            WHERE Amount IS NOT NULL
        ),
              OperationName =
        (
            SELECT OperationName
            FROM
            (
                SELECT KK.Amount,
                       KK.OperationName
                FROM
                (
                    SELECT R.Col1,
                           R.RateID,
                           JJ.Type,
                           FLOOR(JJ.AmountMin + RAND() * (JJ.AmountMax + 1 - JJ.AmountMin)) AS Amount,
                           JJ.OperationName,
                           JJ.Currency
                    FROM Rates R
                         LEFT JOIN
                    (
                        SELECT *
                        FROM OriginTransacts OT
                        WHERE OT.Period = 'Month'
                              AND OT.OperationName = 'Clothes Shopping'
                    ) AS JJ ON R.RateID = FLOOR(RAND() * 5 + 10)
                ) AS KK
            ) AS LL
            WHERE Amount IS NOT NULL
        )
        WHERE Rates.RateID = @i
              AND Rates.Amount IS NULL;
        SET @i+=30;
    END;
    • 3

  2. 我认为有必要首先生成一些具有所需步骤的标识符，然后在更新时应用它。例子：

    DECLARE @BeginID INT = 100 -- 起始 ID 值
  , @EndID INT = 500 -- 结束 ID 值
  , @Step INT = 30;  -  步

带 CTE (id)
作为 （
  选择@BeginID

  联合所有

  选择 id + @Step
  来自 CTE
  WHERE id + @Step <= @EndID
  )

更新率设置
  -- 你的字段更新代码
来自 CTE
其中 Rates.RateID = CTE.ID
选项（最大递归 0）
    • 1

相关问题