求救

[xiaowen31237]

如何查询一个月中某人的连续刷卡记录大于6天的记录，一天多笔算一笔。例如：一个人 1-7号每天都有刷卡，8-9号没有，10-30号每天也有。那么就需要查询出这个人的两条记录 1-7号一条 10-30 号 一条 格式为： ID 姓名 起日期 止日期 连续刷卡天数
表结构 表A ID 姓名 表B ID 刷卡日期 表A 和表B通过ID关联

[Mr_Nice]

体力活，楼下详细了...

[孤独加百列]

CREATE TABLE A
(
	ID INT NOT NULL,
	Name VARCHAR(100) NOT NULL
)

GO

INSERT 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)
)

GO

INSERT 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

[孤独加百列]

[Quote=引用 2 楼 的回复:]

SQL code

CREATE TABLE A
(
ID INT NOT NULL,
Name VARCHAR(100) NOT NULL
)

GO

INSERT INTO A
SELECT 1,'haha' UNION
SELECT 2,'hoho' UNION
SELECT 3,'hehe' UNION
SELECT 4,'hihi'


CREATE TABLE ……
[/Quote]

如果一天多条算一条的话

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

[xiaowen31237]

搞定.....谢谢 给分！