~~~~~~~~很难的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()):累计时间

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

;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