一张log表字段如下：
id | user_id | date | action
action值可为0或者1
每日每用户可有多次action
要统计的是每日，在那日之前一共有多少次action为1的操作
比如 2012-05-01之前一共有多少次action为1的操作，我会算每天的，不会算每天之前的总和。
希望各位高手指教
------解决方案--------------------
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'logtable')BEGIN DROP TABLE logtableENDGOCREATE TABLE logtable( id INT, userid INT, actdate VARCHAR(10), Doaction INT)GOINSERT INTO logtableSELECT 1,1,'2012-05-07',1 UNIONSELECT 2,1,'2012-05-07',0 UNIONSELECT 3,1,'2012-05-07',1 UNIONSELECT 4,1,'2012-05-08',1 UNIONSELECT 5,1,'2012-05-09',1 UNIONSELECT 6,2,'2012-05-07',1 UNIONSELECT 7,2,'2012-05-08',1 UNIONSELECT 8,2,'2012-05-08',1 UNIONSELECT 9,2,'2012-05-09',1 UNIONSELECT 10,2,'2012-05-09',1 UNIONSELECT 11,2,'2012-05-09',1select userid,actdate,SUM(case when Doaction=1 then 1 else 0 end) as timesfrom logtable where actdate<=getdate()--得到系统当前日期group by userid,actdateorder by userid,actdate/*userid actdate times1 2012-05-07 21 2012-05-08 12 2012-05-07 12 2012-05-08 2*/借楼上测试数据一用。我觉得你的统计结果存在问题SELECT 1,1,'2012-05-07',1 UNION--有这一行数据存在1 2012-05-07 0--这一行不对希望解释一下，是我没明白楼主的意思还是怎么的。谢谢了
------解决方案--------------------
- SQL code
select *,(count(*) from log where userid=a.userid and [date]<a.[date] and action=1) from log a where [date] <'2012-05-01'
------解决方案--------------------
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'logtable')BEGIN DROP TABLE logtableENDGOCREATE TABLE logtable( id INT, userid INT, actdate VARCHAR(10), Doaction INT)GOINSERT INTO logtableSELECT 1,1,'2012-05-07',1 UNIONSELECT 2,1,'2012-05-07',0 UNIONSELECT 3,1,'2012-05-07',1 UNIONSELECT 4,1,'2012-05-08',1 UNIONSELECT 5,1,'2012-05-09',1 UNIONSELECT 6,2,'2012-05-07',1 UNIONSELECT 7,2,'2012-05-08',1 UNIONSELECT 8,2,'2012-05-08',1 UNIONSELECT 9,2,'2012-05-09',1 UNIONSELECT 10,2,'2012-05-09',1 UNIONSELECT 11,2,'2012-05-09',1--5月7日之前，不包括当日SELECT DISTINCT actdate,CASE WHEN (SELECT SUM(Doaction) FROM logtable AS B WHERE A.actdate > B.actdate) IS NULL THEN 0 ELSE (SELECT SUM(Doaction) FROM logtable AS B WHERE A.actdate > B.actdate) END AS 操作次数FROM logtable AS Aactdate 操作次数2012-05-07 02012-05-08 32012-05-09 6--5月7日之前，包括当日SELECT DISTINCT actdate,CASE WHEN (SELECT SUM(Doaction) FROM logtable AS B WHERE A.actdate >= B.actdate) IS NULL THEN 0 ELSE (SELECT SUM(Doaction) FROM logtable AS B WHERE A.actdate >= B.actdate) END AS 操作次数FROM logtable AS Aactdate 操作次数2012-05-07 32012-05-08 62012-05-09 10