硬 tsql 问题 - 有多少行值按顺序排列

Question

假设我有一个表

date,personid
1/1/2001 1
1/2/2001 3
1/3/2001 2
1/4/2001 2
1/5/2001 5
1/6/2001 5
1/7/2001 6

，我将使用 personid 2 更新 1/2/2001 或 1/5/2001，但在更新之前，我必须确保它通过了一条规则，表明您不能拥有一个人连续三天。 我该如何在 mssql 存储过程中解决这个问题？

更新：它还需要解决这个布局以及我更新 1/5/2001 的地方

date,personid
1/1/2001 1
1/2/2001 3
1/3/2001 2
1/4/2001 2
1/5/2001 1
1/6/2001 2
1/7/2001 2
1/8/2001 5
1/9/2001 5
1/10/2001 6

Answer 1

我假设 date 是唯一的，如果情况并非如此，请告诉我！

DECLARE @basedata TABLE ([date] UNIQUE DATE,personid INT)
INSERT INTO @basedata
SELECT GETDATE()+1, 2 union all
SELECT GETDATE()+2, 3 union all
SELECT GETDATE()+3, 2 union all
SELECT GETDATE()+4, 2 union all
SELECT GETDATE()+5, 5 union all
SELECT GETDATE()+6, 5 union all
SELECT GETDATE()+7, 6


DECLARE @date date = GETDATE()+5
DECLARE @personid int = 2


;WITH T AS
(
SELECT TOP 2 [date],personid   
FROM @basedata 
WHERE [date] < @date
ORDER BY [date] DESC
UNION ALL
SELECT @date, @personid 
UNION ALL
SELECT TOP 2 [date],personid   
FROM @basedata
WHERE [date] > @date
ORDER BY [date]   
),T2 AS
(
SELECT *, 
       ROW_NUMBER() OVER (ORDER BY [date]) - 
       ROW_NUMBER() OVER (PARTITION BY personid ORDER BY [date]) AS Grp
FROM T
)
SELECT COUNT(*) /*Will return a result if that date/personid 
                  would cause a sequence of 3*/
FROM T2 
GROUP BY personid,Grp
HAVING COUNT(*) >=3

Answer 2

还有第三种情况没有列出，它是间隔日期的情况。我将其包含在下面的解决方案中。

输出是

PersonId    TrackDate  UnallowedBefore UnallowedAfter
----------- ---------- --------------- --------------
2           01/04/2001 01/02/2001      01/05/2001
5           01/06/2001 01/04/2001      01/07/2001
6           01/08/2001 01/08/2001      01/08/2001

USE tempdb
GO
IF OBJECT_ID('PersonDates') IS NOT NULL DROP TABLE PersonDates
CREATE TABLE PersonDates
(
  PersonId  int      NOT NULL,
  TrackDate datetime NOT NULL
)

INSERT INTO PersonDates
(
  TrackDate, 
  PersonId
)
SELECT '1/1/2001', 1
UNION ALL
SELECT '1/2/2001', 3
UNION ALL
SELECT '1/3/2001', 2
UNION ALL
SELECT '1/4/2001', 2
UNION ALL
SELECT '1/5/2001', 5
UNION ALL
SELECT '1/6/2001', 5
UNION ALL
SELECT '1/7/2001', 6
UNION ALL
SELECT '1/8/2001', 2
UNION ALL
SELECT '1/9/2001', 6

SELECT
  P.PersonId,
  TrackDate = CONVERT(varchar(10), DATEADD(day,  1, P.TrackDate), 101),
  T.UnallowedBefore,
  T.UnallowedAfter
FROM
  PersonDates P

  CROSS APPLY
  (
    SELECT TOP 1 
      UnallowedAfter  = CASE
        WHEN DATEDIFF(day, P.TrackDate, TrackDate) = 1
        THEN CONVERT(varchar(10), DATEADD(day,  1, TrackDate), 101)
        ELSE CONVERT(varchar(10), DATEADD(day, -1, TrackDate), 101)
      END,
      UnallowedBefore = CASE
        WHEN DATEDIFF(day, P.TrackDate, TrackDate) = 1
        THEN CONVERT(varchar(10), DATEADD(day, -2, TrackDate), 101)
        ELSE CONVERT(varchar(10), DATEADD(day, -1, TrackDate), 101)
      END
    FROM 
      PersonDates 
    WHERE 
      PersonId = P.PersonId
      AND
      DATEDIFF(day, P.TrackDate, TrackDate) IN (1,2)
  ) T

Answer 3

SET @TargetDate = '1/2/2001'

SELECT @ForwardCount = COUNT(*) FROM table WHERE ([date] BETWEEN @TargetDate AND DATEADD(dd, 2, @TargetDate)) WHERE PersonID = @PersonID

SELECT @BackwardCount = COUNT(*) FROM table WHERE ([date] BETWEEN @TargetDate AND DATEADD(dd, -2, @TargetDate)) WHERE PersonID = @PersonID

SELECT @BracketCount = COUNT(*) FROM table WHERE ([date] BETWEEN DATEADD(dd, -1, @TargetDate) AND DATEADD(dd, 1, @TargetDate)) WHERE PersonID = @PersonID

IF (@ForwardCount < 2) AND (@BackwardCount < 2) AND (@BracketCount < 2)
BEGIN
-- Do your update here
END

Answer 4

这是我的参数化解决方案：

WITH nearby AS (
  SELECT
    date,
    personid = CASE date WHEN @date THEN @personid ELSE personid END
  FROM atable
  WHERE date BETWEEN DATEADD(day, -@MaxInARow, @date)
                 AND DATEADD(day,  @MaxInARow, @date)
),
nearbyGroups AS (
  SELECT
    *,
    Grp = DATEDIFF(day, 0, date) -
          ROW_NUMBER() OVER (PARTITION BY personid ORDER BY date)
  FROM nearby
)
UPDATE atable
SET personid = @personid
WHERE date = @date
  AND NOT EXISTS (
    SELECT Grp 
    FROM nearbyGroups
    GROUP BY Grp
    HAVING COUNT(*) > @MaxInARow
  )

@date 表示应更新 personid 列的日期。 @personid 是要存储的新值。 @MaxInARow 是允许存储相同personid 的连续最大天数。