~~~~~~~~很难的SQL统计语句，怎么写？~~~~~~~~~~~~

[hxm20003]

obj_id obj_date value
1 2015-07-30 15:00:00.000 1
1 2015-07-30 15:02:00.000 1
1 2015-07-30 15:04:00.000 1
1 2015-07-30 15:06:00.000 0
1 2015-07-30 15:08:00.000 0
1 2015-07-30 15:10:00.000 1
1 2015-07-30 15:20:00.000 0
2 2015-07-30 15:02:00.000 1

value第一次出现1开始累计到value=0为止，如果0一直没有出现，就和系统时间比。
现在系统时间比如说是15:30

结果
id 开始时间 累计时间
1 2015-07-30 15:00:00.000 6
1 2015-07-30 15:10:00.000 10
2 2015-07-30 15:02:00.000 28

[Haytor]

可以参考一下这个脚本

;WITH Tab1(obj_id,obj_date,value) AS (
	SELECT 1,'2015-07-30 15:00:00.000',1 UNION ALL
	SELECT 1,'2015-07-30 15:02:00.000',1 UNION ALL
	SELECT 1,'2015-07-30 15:04:00.000',1 UNION ALL
	SELECT 1,'2015-07-30 15:06:00.000',0 UNION ALL
	SELECT 1,'2015-07-30 15:08:00.000',0 UNION ALL
	SELECT 1,'2015-07-30 15:10:00.000',1 UNION ALL
	SELECT 1,'2015-07-30 15:20:00.000',0 UNION ALL
	SELECT 2,'2015-07-30 15:02:00.000',1 
),
Tab2 AS (
	SELECT 
		*,ROW_NUMBER() OVER(PARTITION BY obj_id ORDER BY obj_date) AS RowID
	FROM Tab1
),
Tab3 AS (
	SELECT 
		*,ROW_NUMBER() OVER(PARTITION BY obj_id ORDER BY obj_date) AS RowID
	FROM Tab1
	WHERE value = 1
),
Tab4 AS (
	SELECT
		a.obj_id,
		a.obj_date,
		ISNULL((cc.obj_date),CONVERT(NVARCHAR(max),GETDATE(),121)) NextDate,
		(a.RowID-b.RowID) AS diff 
	FROM Tab2 a INNER JOIN Tab3 b ON a.obj_date = b.obj_date AND a.obj_id = b.obj_id
	OUTER APPLY( SELECT TOP 1 c.obj_date FROM Tab1 c 
				 WHERE c.obj_id = a.obj_id AND c.obj_date>a.obj_date 
				 ORDER BY c.obj_date ) cc
)
SELECT
	 a.obj_id,
	 MIN(a.obj_date) AS obj_date,
	 DATEDIFF(MI,MIN(a.obj_date),MAX(a.NextDate)) AS SumMin
FROM 
	Tab4 a
GROUP BY 
	a.obj_id,a.diff
ORDER BY
	a.obj_id

[Tiger_Zhao]

DECLARE @now datetime
SET @now = '2015-07-30 15:30:00'
-- 实际应用时用 GETDATE() 代替下面语句中的 @now

;WITH /* 册数数据
table1(obj_id,obj_date,value)AS (
    SELECT 1,'2015-07-30 15:00:00.000',1 UNION ALL
    SELECT 1,'2015-07-30 15:02:00.000',1 UNION ALL
    SELECT 1,'2015-07-30 15:04:00.000',1 UNION ALL
    SELECT 1,'2015-07-30 15:06:00.000',0 UNION ALL
    SELECT 1,'2015-07-30 15:08:00.000',0 UNION ALL
    SELECT 1,'2015-07-30 15:10:00.000',1 UNION ALL
    SELECT 1,'2015-07-30 15:20:00.000',0 UNION ALL
    SELECT 2,'2015-07-30 15:02:00.000',1
), */
a AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY obj_id
                             ORDER BY obj_date
                            ) rn1,
           ROW_NUMBER() OVER(PARTITION BY obj_id
                             ORDER BY value,obj_date
                            ) rn2
      FROM table1
)
,b AS (
    SELECT obj_id,
           rn1-rn2 g,
           value,
           MIN(obj_date) obj_date,
           ROW_NUMBER() OVER(PARTITION BY obj_id
                             ORDER BY MIN(obj_date)
                            ) rn
      FROM a
  GROUP BY obj_id, rn1-rn2, value
)
    SELECT b1.obj_id id,
           b1.obj_date 开始时间,
           DATEDIFF(minute,
                    b1.obj_date,
                    ISNULL(b2.obj_date, @now)) 累计时间
      FROM b b1
 LEFT JOIN b b2
        ON b1.obj_id = b2.obj_id
       AND b1.rn + 1 = b2.rn
     WHERE b1.value = 1
   ORDER BY b1.obj_id, b1.rn

         id 开始时间                   累计时间
----------- ----------------------- -----------
          1 2015-07-30 15:00:00.000           6
          1 2015-07-30 15:10:00.000          10
          2 2015-07-30 15:02:00.000          28

[xxfvba]

--写得有点麻烦 ;WITH T1(obj_id,obj_date,value) AS ( SELECT 1,'2015-07-30 15:00:00.000',1 UNION ALL SELECT 1,'2015-07-30 15:02:00.000',1 UNION ALL SELECT 1,'2015-07-30 15:04:00.000',1 UNION ALL SELECT 1,'2015-07-30 15:06:00.000',0 UNION ALL SELECT 1,'2015-07-30 15:08:00.000',0 UNION ALL SELECT 1,'2015-07-30 15:10:00.000',1 UNION ALL SELECT 1,'2015-07-30 15:20:00.000',0 UNION ALL SELECT 2,'2015-07-30 15:02:00.000',1 ), T as (select *,rn=row_number() over (partition by obj_id order by obj_date) from T1) select id,tim as [开始时间],datediff(mi,tim,case when tim1 is null then getdate() else tim1 end) as [累计时间] from (select a.id,a.tim,(select min(obj_date) from T where a.id=t.obj_id and a.rn<T.rn and [value]=0) as tim1 from (select obj_id as id,min(rn) as rn,min(obj_date) tim from (select *,rn1=row_number() over (partition by obj_id order by rn) from T where [value]=1) a group by obj_id,rn-rn1) a) a order by id,tim

[刨坑数据]

按相邻成员的value分组，取每两组的首条记录即可获取开始时间和计算时长，SQL搞这些序运算很难懂，数据量不大时读出来用支持集算器写起来比较简单：

A1	$select * from tb order by obj_id,obj_date
A2 =A1.group(obj_id).(~.group@o1(value)|[null])
A3 =A2.news(~.len()\2;~.obj_id:id,(s=~(#*2-1).obj_date):开始时间, interval@s(s,ifn(~(#*2).obj_date,now()):累计时间

其中A3即为所得结果：

这段代码的集成方法可参考http://blog.raqsoft.cn/?p=5750 .