MS-SQL中取用户连续签到的次数

表结构如下：

每个用户每天只能签到一次

现在前面的需求是判断某个用户在某天是否是连续签到，使用sql中的递归来实现

with currentDateCTE AS (
 -- 当前天.
 SELECT
  *
 FROM
  dt_Signin
 WHERE
  user_id = 270 and CONVERT(DATE, sign_time)  = CONVERT(DATE, '2013-05-16 22:15:32.670')--这就是需求中的某天
),
prevDateCTE AS (
 -- 向前递归.
 SELECT
  *
 FROM
  currentDateCTE
 UNION ALL
 SELECT
  prevTab.*
 FROM
 (select * from dt_Signin  where user_id = 270) as  prevTab JOIN  prevDateCTE  
   ON (CONVERT(DATE,prevTab.sign_time)  = CONVERT(DATE,convert(char(20),dateadd(day,-1,prevDateCTE.sign_time),102))) 
),
nextDateCTE AS (
 -- 向后递归.
 SELECT
  *
 FROM
  currentDateCTE
 UNION ALL
 SELECT
  prevTab.*
 FROM
  (select * from dt_Signin  where user_id = 270) as prevTab JOIN  nextDateCTE  
   ON (CONVERT(DATE,prevTab.sign_time)  = CONVERT(DATE,convert(char(20),dateadd(day,+1,nextDateCTE.sign_time),102)))
)
SELECT
 --(SELECT MIN(sign_time) FROM prevDateCTE)  AS [第一天],
 --(SELECT MAX(sign_time) FROM nextDateCTE)  AS [最后一天],  
 DATEDIFF ( 
  dd,
  (SELECT MIN(sign_time) FROM prevDateCTE),
  (SELECT MAX(sign_time) FROM nextDateCTE)  
  ) + 1  AS signlxcount ---查询连续签到次数

原文如下：

判断 “存在连续天数” 的处理

最近遇到一个问题， 说某个打卡的系统， 要打卡以后， 判断是否存在 连续7天打卡。
例如 2012年1月7日 打卡了， 那么去检查 2012年1月1日 至 2012年1月6日 是否都打了。
还存在这样的情况， 可能是 2012年1月4日 至 2012年1月6日都打了， 同时 2012年1月8日 至 2012年1月10日都打了。
新打一个 2012年1月7日的卡， 形成了一个 连续7日。 （ 2012年1月4日 -- 2012年1月10日 ）

处理思路是：
1. 从打卡日， 一天一天向前递归，直到遇到 上限值（例如这里的7天）， 或者遇到中断。
2. 从打卡日， 一天一天向后递归，直到遇到 上限值（例如这里的7天）， 或者遇到中断。
3. 获取前面两个步骤所产生的： 向前递归的最小值， 与向后递归的最大值。 来计算， 打卡日作产生的 连续天数。

CREATE TABLE #t (
id INT identity(1,1),
test_date DATETIME
)

INSERT INTO #t
SELECT CONVERT(DATETIME, '20120101', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120102', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120103', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120104', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120105', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120106', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120107', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120108', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120110', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120111', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120112', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120113', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120114', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120115', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120116', 112) UNION ALL
SELECT CONVERT(DATETIME, '20120117', 112);

with currentDateCTE AS (
-- 当前天.
SELECT
*
FROM
#t
WHERE
test_date = CONVERT(DATETIME, '20120106', 112)
),
prevDateCTE AS (
-- 向前递归.
SELECT
*
FROM
currentDateCTE
UNION ALL
SELECT
prevTab.*
FROM
#t prevTab JOIN prevDateCTE
ON (prevTab.test_date = prevDateCTE.test_date - 1)
),
nextDateCTE AS (
-- 向后递归.
SELECT
*
FROM
currentDateCTE
UNION ALL
SELECT
prevTab.*
FROM
#t prevTab JOIN nextDateCTE
ON (prevTab.test_date = nextDateCTE.test_date + 1)
)
SELECT
(SELECT MIN(test_date) FROM prevDateCTE) AS [第一天],
(SELECT MAX(test_date) FROM nextDateCTE) AS [最后一天],
DATEDIFF (
dd,
(SELECT MIN(test_date) FROM prevDateCTE),
(SELECT MAX(test_date) FROM nextDateCTE)
) + 1 AS [存在的连续日期]

转载URL：http://hi.baidu.com/wangzhiqing999/item/2abb8bf3aeeb4a7d3c198b46