日期:2014-05-17 浏览次数:20689 次
SELECT 号码,时间,次数=(ROW_NUMBER()OVER(PARTITION BY 号码,ORDER BY 时间))-1 FROM TABLEA ), A2 AS( SELECT 号码,MIN(时间) FROM TABLEA GROUP BY 号码
------解决方案--------------------
DECLARE @t table ( number varchar(10), cdate datetime ) INSERT INTO @t SELECT '123', '2012/2/13 16:23:12' UNION SELECT '234', '2012/2/25 16:23:12' UNION SELECT '123', '2012/5/13 16:23:12' UNION SELECT '235', '2011/2/13 16:23:12' UNION SELECT '321', '2012/1/15 16:23:12' UNION SELECT '234', '2012/2/17 16:23:12' UNION SELECT '123', '2012/2/13 20:11:01' UNION SELECT '321', '2012/1/10 16:23:12' SELECT t1.number,t1.cdate, COUNT(t2.number)+1 FROM @t t1 LEFT JOIN @t t2 ON t1.number=t2.number AND t1.cdate>t2.cdate GROUP BY t1.number, t1.cdate, t2.number