交易时间
2015-02-10 13:21:52
2015-02-10 15:28:49
2015-02-10 12:33:51
2015-02-10 16:42:39
2015-02-10 17:39:05
SQL命令是
（SELECT 卡号,,账号,
MIN(交易时间) FirstTime
FROM sheet1$
GROUP BY 卡号,账号
) sheet1$
JOIN sheet1$ t
ON t.卡号 = sheet1$.卡号
AND t.交易时间 <= DateAdd(MINUTE,5,sheet1$.FirstTime)
GROUP BY sheet1$.卡号,sheet1$.,账号
------解决思路----------------------
SELECT sheet1$.卡号,sheet1$.,账号
FROM
(SELECT 卡号,,账号,
MIN(交易时间) FirstTime
FROM sheet1$
GROUP BY 卡号,账号
) sheet1$
JOIN sheet1$ t
ON t.卡号 = sheet1$.卡号
AND t.交易时间 <= DateAdd(MINUTE,5,sheet1$.FirstTime)
GROUP BY sheet1$.卡号,sheet1$.,账号
------解决思路----------------------
/* 测试数据
WITH [sheet1$](卡号,账号,交易时间) AS (
SELECT 'AA','A','2015-02-10 12:33:51' UNION ALL
SELECT 'AA','B','2015-02-10 19:33:51' UNION ALL
SELECT 'AA','C','2015-02-10 17:39:05' UNION ALL
SELECT 'AA','M','2015-02-10 19:17:49' UNION ALL
SELECT 'AA','Q','2015-02-10 13:21:52' UNION ALL
SELECT 'BB','N','2015-02-10 19:18:43' UNION ALL
SELECT 'BB','O','2015-02-10 19:18:15' UNION ALL
SELECT 'BB','P','2015-02-10 19:43:17' UNION ALL
SELECT 'BB','P','2015-02-10 19:52:20' UNION ALL
SELECT 'BB','P','2015-02-10 19:47:50' UNION ALL
SELECT 'CC','E','2015-02-10 16:42:39' UNION ALL
SELECT 'CC','F','2015-02-10 15:24:04' UNION ALL
SELECT 'CC','G','2015-02-10 20:03:16' UNION ALL
SELECT 'CC','G','2015-02-10 15:25:24' UNION ALL
SELECT 'CC','G','2015-02-10 20:06:27' UNION ALL
SELECT 'CC','K','2015-02-10 19:16:37' UNION ALL
SELECT 'CC','K','2015-02-10 19:59:18' UNION ALL
SELECT 'CC','L','2015-02-10 20:02:04' UNION ALL
SELECT 'CC','L','2015-02-10 19:53:47' UNION ALL
SELECT 'DD','D','2015-02-10 15:28:49' UNION ALL
SELECT 'DD','H','2015-02-10 15:24:33' UNION ALL
SELECT 'DD','H','2015-02-10 15:23:43' UNION ALL
SELECT 'DD','I','2015-02-10 15:25:00'
)*/
SELECT *
FROM [sheet1$] t1
CROSS APPLY (
SELECT COUNT(DISTINCT 账号) 卡号交易过账号次数
FROM [sheet1$] t2
WHERE t2.卡号 = t1.卡号
AND t2.交易时间 BETWEEN DATEADD(minute,-5,t1.交易时间)
AND DATEADD(minute,5,t1.交易时间)
) s
WHERE s.卡号交易过账号次数 > 1
卡号 账号 交易时间 卡号交易过账号次数
---- ---- ------------------- ------------------
BB N 2015-02-10 19:18:43 2
BB O 2015-02-10 19:18:15 2
CC F 2015-02-10 15:24:04 2
CC G 2015-02-10 20:03:16 3
CC G 2015-02-10 15:25:24 2
CC G 2015-02-10 20:06:27 2
CC K 2015-02-10 19:59:18 3
CC L 2015-02-10 20:02:04 3
DD D 2015-02-10 15:28:49 3
DD H 2015-02-10 15:24:33 3
DD H 2015-02-10 15:23:43 2
DD I 2015-02-10 15:25:00 3