SQL计数连续几天

前端之家收集整理的这篇文章主要介绍了SQL计数连续几天前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
这是sql数据库数据:
  1. UserTable
  2.  
  3. UserName | UserDate | UserCode
  4. -------------------------------------------
  5. user1 | 08-31-2014 | 232
  6. user1 | 09-01-2014 | 232
  7. user1 | 09-02-2014 | 0
  8. user1 | 09-03-2014 | 121
  9. user1 | 09-08-2014 | 122
  10. user1 | 09-09-2014 | 0
  11. user1 | 09-10-2014 | 144
  12. user1 | 09-11-2014 | 166
  13. user2 | 09-01-2014 | 177
  14. user2 | 09-04-2014 | 188
  15. user2 | 09-05-2014 | 199
  16. user2 | 09-06-2014 | 0
  17. user2 | 09-07-2014 | 155

如果[UserCode]不是零,则应仅计算连续天数(如果为结果).
UserDate介于09-01-2014和09-11-2014之间.
仅在结果为2或更多时显示结果.

我希望我的SQL查询返回的是:

  1. UserName | StartDate | EndDate | Result
  2. ----------------------------------------------------------
  3. user1 | 09-01-2014 | 09-03-2014 | 2
  4. user1 | 09-08-2014 | 09-11-2014 | 3
  5. user2 | 09-04-2014 | 09-07-2014 | 3

这只能使用SQL查询吗?

解决方法

这是一个 Gaps and Islands问题.解决此问题的最简单方法是使用ROW_NUMBER()来识别序列中的间隙:
  1. SELECT UserName,UserDate,UserCode,GroupingSet = DATEADD(DAY,-ROW_NUMBER() OVER(PARTITION BY UserName
  2. ORDER BY UserDate),UserDate)
  3. FROM UserTable;

这给出了:

  1. UserName | UserDate | UserCode | GroupingSet
  2. ------------+---------------+------------+-------------
  3. user1 | 09-01-2014 | 1 | 08-31-2014
  4. user1 | 09-02-2014 | 0 | 08-31-2014
  5. user1 | 09-03-2014 | 1 | 08-31-2014
  6. user1 | 09-08-2014 | 1 | 09-04-2014
  7. user1 | 09-09-2014 | 0 | 09-04-2014
  8. user1 | 09-10-2014 | 1 | 09-04-2014
  9. user1 | 09-11-2014 | 1 | 09-04-2014
  10. user2 | 09-01-2014 | 1 | 08-31-2014
  11. user2 | 09-04-2014 | 1 | 09-02-2014
  12. user2 | 09-05-2014 | 1 | 09-02-2014
  13. user2 | 09-06-2014 | 0 | 09-02-2014
  14. user2 | 09-07-2014 | 1 | 09-02-2014

如您所见,这为连续行的GroupingSet提供了一个常量值.然后,您可以按此列分组以获取所需的摘要

  1. WITH CTE AS
  2. ( SELECT UserName,-ROW_NUMBER() OVER(PARTITION BY UserName
  3. ORDER BY UserDate),UserDate)
  4. FROM UserTable
  5. )
  6. SELECT UserName,StartDate = MIN(UserDate),EndDate = MAX(UserDate),Result = COUNT(NULLIF(UserCode,0))
  7. FROM CTE
  8. GROUP BY UserName,GroupingSet
  9. HAVING COUNT(NULLIF(UserCode,0)) > 1
  10. ORDER BY UserName,StartDate;

Example on SQL Fiddle

猜你在找的MsSQL相关文章