sql 查询 按小时 分组数据解决方法

sql 查询 按小时 分组数据
我有这样表
id  int
equ_Code  varchar(25)
equ_Name  nvarchar(255)
flux   float 
recordTime   datetime

要查出一天内每小时 flux的值。flux 每小时的值等于当前小时内 最后条-当前小时内第一条

原始数据：
id equ_Code equ_Name flux recordTime
0 F1001 xxxxx         1004 2015-04-16 00:00:00.000
1 F1001 xxxxx 1006 2015-04-16 00:05:00.000
2 F1001 xxxxx 1004 2015-04-16 00:10:00.000
3 F1001 xxxxx 1004 2015-04-16 00:15:00.000
4 F1001 xxxxx 1004 2015-04-16 00:20:00.000
5 F1001 xxxxx 1010 2015-04-16 00:25:00.000
6 F1001 xxxxx 1014 2015-04-16 00:30:00.000
7 F1001 xxxxx 1014 2015-04-16 00:35:00.000
8 F1001 xxxxx 1016 2015-04-16 00:40:00.000
9 F1001 xxxxx 1026 2015-04-16 00:45:00.000
10 F1001 xxxxx  1036 2015-04-16 00:50:00.000
11 F1001 xxxxx  1046 2015-04-16 00:55:00.000
12 F1001 xxxxx          1056   2015-04-16 01:00:00.000

那么 0点内的数据 就是 1046-1004=42
结果这样



小时     equ_code equ_name flux  
0           0001            “XXX”         0.999
1          0001            “XXX”         1.999
..

sql 语句怎么写 。求大神帮忙看看那！
------解决思路----------------------

;WITH CTE AS(

	SELECT id,equ_Code,equ_Name,flux,recordTime,CONVERT(VARCHAR(13),recordTime,120)[hourstr]

		,ROW_NUMBER()OVER(PARTITION BY equ_Code,equ_name,CONVERT(VARCHAR(13),recordTime,120)ORDER BY recordTime)RN

	FROM TB

)

,CTE2 AS(

	SELECT [hourstr],equ_Code,equ_name

		,MIN(RN)MINRN

		,MAX(RN)MAXRN

	FROM CTE

	GROUP BY equ_Code,equ_name,[hourstr]

)

SELECT CAST(RIGHT(T1.[hourstr],2)AS INT)[小时]

	,T1.equ_Code,T1.equ_name

	,T3.flux-T2.flux[flux]

FROM CTE2 T1

	JOIN CTE T2 ON T1.equ_Code=T2.equ_Code AND T1.equ_name=T2.equ_name AND T1.[hourstr]=T2.[hourstr]AND T1.MINRN=T2.RN

	JOIN CTE T3 ON T1.equ_Code=T3.equ_Code AND T1.equ_name=T3.equ_name AND T1.[hourstr]=T3.[hourstr]AND T1.MAXRN=T3.RN

------解决思路----------------------

WITH /* 测试数据

table1(id,equ_Code,equ_Name,flux,recordTime) as (

    SELECT 0,'F1001','xxxxx',1004,'2015-04-16 00:00:00.000' UNION ALL

    SELECT 1,'F1001','xxxxx',1006,'2015-04-16 00:05:00.000' UNION ALL

    SELECT 2,'F1001','xxxxx',1004,'2015-04-16 00:10:00.000' UNION ALL

    SELECT 3,'F1001','xxxxx',1004,'2015-04-16 00:15:00.000' UNION ALL

    SELECT 4,'F1001','xxxxx',1004,'2015-04-16 00:20:00.000' UNION ALL

    SELECT 5,'F1001','xxxxx',1010,'2015-04-16 00:25:00.000' UNION ALL

    SELECT 6,'F1001','xxxxx',1014,'2015-04-16 00:30:00.000' UNION ALL

    SELECT 7,'F1001','xxxxx',1014,'2015-04-16 00:35:00.000' UNION ALL

    SELECT 8,'F1001','xxxxx',1016,'2015-04-16 00:40:00.000' UNION ALL

    SELECT 9,'F1001','xxxxx',1026,'2015-04-16 00:45:00.000' UNION ALL

    SELECT 10,'F1001','xxxxx',1036,'2015-04-16 00:50:00.000' UNION ALL

    SELECT 11,'F1001','xxxxx',1046,'2015-04-16 00:55:00.000' UNION ALL

    SELECT 12,'F1001','xxxxx',1056,'2015-04-16 01:00:00.000'

), */

t1 AS (

    SELECT DatePart(hour,recordTime) 小时,

           equ_code,

           equ_name,

           flux,

           recordTime

      FROM table1

     WHERE recordTime >= '2015-04-16'

       AND recordTime <  '2015-04-17'

)

,s AS (

    SELECT DISTINCT

           小时,

           equ_code,

           equ_name

      FROM t1

)

        SELECT s.*,

               tMax.flux-tMin.flux flux

          FROM s

   CROSS APPLY (

                    SELECT TOP 1 *

                      FROM t1

                     WHERE 小时 = s.小时

                       AND equ_code = s.equ_code

                       AND equ_name = s.equ_name

                  ORDER BY recordTime

               ) tMin

   CROSS APPLY (

                    SELECT TOP 1 *

                      FROM t1

                     WHERE 小时 = s.小时

                       AND equ_code = s.equ_code

                       AND equ_name = s.equ_name

                  ORDER BY recordTime DESC

               ) tMax

       小时 equ_code equ_name        flux

----------- -------- -------- -----------

          0 F1001    xxxxx             42

          1 F1001    xxxxx              0

-- 如果 flux 一直是增量用这个

    SELECT DatePart(hour,recordTime) 小时,

           equ_code,

           equ_name,

           MAX(flux)-MIN(flux) flux

      FROM table1

     WHERE recordTime >= '2015-04-16'

       AND recordTime <  '2015-04-17'

  GROUP BY DatePart(hour,recordTime),equ_code,equ_name

------解决思路----------------------
不知道去不去掉F

--不去掉F

select datepart(hour,recordtime) as 小时,equ_code,equ_name,max(flux)-min(flux) as flux

from 表名

group by equ_code,equ_name,datepart(hour,recordtime)



--去掉F

select datepart(hour,recordtime) as 小时,right(equ_code,len(equ_code)-1),equ_name,max(flux)-min(flux) as flux

from 表名

group by right(equ_code,len(equ_code)-1),equ_name,datepart(hour,recordtime)