如何查询一个月中某人的连续刷卡记录大于6天的记录,一天多笔算一笔。例如:一个人 1-7号每天都有刷卡,8-9号没有,10-30号每天也有。那么就需要查询出这个人的两条记录 1-7号一条 10-30 号 一条 格式为: ID 姓名 起日期 止日期 连续刷卡天数
表结构 表A ID 姓名 表B ID 刷卡日期 表A 和表B通过ID关联
表结构 表A ID 姓名 表B ID 刷卡日期 表A 和表B通过ID关联
CREATE TABLE A
(
ID INT NOT NULL,
Name VARCHAR(100) NOT NULL
)GOINSERT INTO A
SELECT 1,'haha' UNION
SELECT 2,'hoho' UNION
SELECT 3,'hehe' UNION
SELECT 4,'hihi'
CREATE TABLE B
(
ID INT NOT NULL,
ShoppingDate VARCHAR(100)
)GOINSERT INTO B
SELECT 1,'2012-04-01 12:00:00' UNION
SELECT 1,'2012-04-02 12:00:00' UNION
SELECT 1,'2012-04-03 12:00:00' UNION
SELECT 1,'2012-04-04 12:00:00' UNION
SELECT 1,'2012-04-05 12:00:00' UNION
SELECT 1,'2012-04-06 12:00:00' UNION
SELECT 1,'2012-04-07 12:00:00' UNION
SELECT 1,'2012-04-08 12:00:00' UNION
SELECT 1,'2012-04-10 12:00:00' UNION
SELECT 1,'2012-04-11 12:00:00' UNION
SELECT 1,'2012-04-13 12:00:00' UNION
SELECT 1,'2012-04-14 12:00:00' UNION
SELECT 1,'2012-04-15 12:00:00' UNION
SELECT 1,'2012-04-16 12:00:00' UNION
SELECT 1,'2012-04-17 12:00:00' UNION
SELECT 1,'2012-04-18 12:00:00' UNION
SELECT 1,'2012-04-19 12:00:00' UNION
SELECT 1,'2012-04-20 12:00:00' UNION
SELECT 1,'2012-04-21 12:00:00' UNION
SELECT 1,'2012-04-22 12:00:00' UNION
SELECT 1,'2012-04-23 12:00:00' UNION
SELECT 1,'2012-04-24 12:00:00' UNION
SELECT 1,'2012-04-25 12:00:00' UNION
SELECT 1,'2012-04-26 12:00:00' UNION
SELECT 1,'2012-04-27 12:00:00' UNION
SELECT 3,'2012-04-01 12:00:00' UNION
SELECT 3,'2012-04-02 12:00:00' UNION
SELECT 3,'2012-04-03 12:00:00' UNION
SELECT 3,'2012-04-04 12:00:00' UNION
SELECT 3,'2012-04-05 12:00:00' UNION
SELECT 3,'2012-04-06 12:00:00' UNION
SELECT 3,'2012-04-07 12:00:00' UNION
SELECT 3,'2012-04-08 12:00:00' UNION
SELECT 2,'2012-04-10 12:00:00' UNION
SELECT 2,'2012-04-11 12:00:00' UNION
SELECT 4,'2012-04-13 12:00:00' UNION
SELECT 4,'2012-04-14 12:00:00' UNION
SELECT 4,'2012-04-15 12:00:00' UNION
SELECT 4,'2012-04-16 12:00:00' UNION
SELECT 4,'2012-04-17 12:00:00' UNION
SELECT 4,'2012-04-18 12:00:00' UNION
SELECT 4,'2012-04-19 12:00:00' UNION
SELECT 4,'2012-04-20 12:00:00' UNION
SELECT 4,'2012-04-21 12:00:00' UNION
SELECT 4,'2012-04-22 12:00:00' UNION
SELECT 4,'2012-04-23 12:00:00' UNION
SELECT 4,'2012-04-24 12:00:00' UNION
SELECT 4,'2012-04-25 12:00:00' UNION
SELECT 4,'2012-04-26 12:00:00' UNION
SELECT 4,'2012-04-27 12:00:00' SELECT C.ID,A.Name,MIN(shoppingdate),MAX(shoppingdate),COUNT(C.ID)
FROM (SELECT ID,shoppingdate,rn=ROW_NUMBER()OVER (ORDER BY Id,shoppingdate) FROM B) AS C,A
WHERE C.ID = A.ID
GROUP BY C.ID,A.Name,DATEADD(DAY,0-rn,shoppingdate)
HAVING COUNT(C.ID) > 6
ORDER BY C.ID
ID Name (No column name) (No column name) (No column name)
1 haha 2012-04-01 12:00:00 2012-04-08 12:00:00 8
1 haha 2012-04-13 12:00:00 2012-04-27 12:00:00 15
3 hehe 2012-04-01 12:00:00 2012-04-08 12:00:00 8
4 hihi 2012-04-13 12:00:00 2012-04-27 12:00:00 15
如果一天多条算一条的话SELECT C.ID,A.Name,MIN(shoppingdate),MAX(shoppingdate),COUNT(C.ID)
FROM (SELECT ID,shoppingdate,rn=ROW_NUMBER()OVER (ORDER BY Id,shoppingdate) FROM (SELECT DISTINCT ID,CONVERT(VARCHAR(10),shoppingdate,23) AS shoppingdate FROM B) AS D) AS C,A
WHERE C.ID = A.ID
GROUP BY C.ID,A.Name,DATEADD(DAY,0-rn,shoppingdate)
HAVING COUNT(C.ID) > 6
ORDER BY C.ID