【关于按小时统计SQL】请各位帮帮忙

Question

表结构如下：MID[设备编号]   AA[浮点型]     BB[某浮点型]        DATE[时间]   
Machine1        5.5             6.5                2012-4-20 12:05:00
Machine1        5.6             6.6                2012-4-20 12:08:00
Machine1        6.7             2.3                2012-4-20 01:10:00
Machine1        99.99           98.88              2012-4-20 01:59:00
Machine1        9.0             3.3                2012-4-20 02:15:00
Machine1        7.2             6.8                2012-4-20 03:25:00
Machine1        5.6             9.8                2012-4-20 04:30:00
Machine1        4.2             4.4                2012-4-20 05:35:00
Machine1        8.8             3.6                2012-4-20 06:40:00
表说明：
MID指的是一个设备编号
每隔几分钟就插入表中一些数据要求查询结果字段            0-1小时内     1-2小时内        2-3小时内       3-4小时内     ....      22-23小时内
AA               5.6            99.99             9.0            5.6
BB               6.6            98.88             3.3            9.8         ....          ....
某个小时内可能存在多条记录，只要查询最新日期的那一条就行

Answer 1

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2012-04-21 07:15:18
--  blog  : blog.csdn.net/herowang
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (MID VARCHAR(8),AA NUMERIC(4,2),BB NUMERIC(4,2),DATE DATETIME)
INSERT INTO [tb]
SELECT 'Machine1',5.5,6.5,'2012-4-20 00:05:00' UNION ALL
SELECT 'Machine1',5.6,6.6,'2012-4-20 00:08:00' UNION ALL
SELECT 'Machine1',6.7,2.3,'2012-4-20 01:10:00' UNION ALL
SELECT 'Machine1',99.99,98.88,'2012-4-20 01:59:00' UNION ALL
SELECT 'Machine1',9.0,3.3,'2012-4-20 02:15:00' UNION ALL
SELECT 'Machine1',7.2,6.8,'2012-4-20 03:25:00' UNION ALL
SELECT 'Machine1',5.6,9.8,'2012-4-20 04:30:00' UNION ALL
SELECT 'Machine1',4.2,4.4,'2012-4-20 05:35:00' UNION ALL
SELECT 'Machine1',8.8,3.6,'2012-4-20 06:40:00'--select *,convert(char(13),date,120)  from [tb]
go
with cte
as(select row=row_number() over(partition by mid,convert(char(13),date,120) order by date desc),* from tb),
cte2 as(select row, mid,ziduan='AA',AA,date from cte where row=1 union select row, mid,'BB',BB,date from cte where row=1 )select ziduan,
[0-1]=max(case when ziduan='AA' and date >= '2012-04-20 00:00:00' and date <'2012-04-20 01:00:00' then AA 
  when ziduan='BB' and date >= '2012-04-20 00:00:00' and date <'2012-04-20 01:00:00' then AA 
  end),
[1-2]=max(case when ziduan='AA' and date >= '2012-04-20 01:00:00' and date <'2012-04-20 02:00:00' then AA 
  when ziduan='BB' and date >= '2012-04-20 01:00:00' and date <'2012-04-20 02:00:00' then AA 
  end),
[2-3]=max(case when ziduan='AA' and date >= '2012-04-20 02:00:00' and date <'2012-04-20 03:00:00' then AA 
  when ziduan='BB' and date >= '2012-04-20 02:00:00' and date <'2012-04-20 03:00:00' then AA 
  end)
…… 剩下的一次类推
from cte2
group by ziduan楼主的工作就是构造'2012-04-20 02:00:00' 时间的起始点，这里用的是固定的日期，

Answer 2

借用一下 HEROWANG 的测试数据。
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (MID VARCHAR(8),AA NUMERIC(4,2),BB NUMERIC(4,2),DATE DATETIME)
INSERT INTO [tb]
SELECT 'Machine1',5.5,6.5,'2012-4-20 00:05:00' UNION ALL
SELECT 'Machine1',5.6,6.6,'2012-4-20 00:08:00' UNION ALL
SELECT 'Machine1',6.7,2.3,'2012-4-20 01:10:00' UNION ALL
SELECT 'Machine1',99.99,98.88,'2012-4-20 01:59:00' UNION ALL
SELECT 'Machine1',9.0,3.3,'2012-4-20 02:15:00' UNION ALL
SELECT 'Machine1',7.2,6.8,'2012-4-20 03:25:00' UNION ALL
SELECT 'Machine1',5.6,9.8,'2012-4-20 04:30:00' UNION ALL
SELECT 'Machine1',4.2,4.4,'2012-4-20 05:35:00' UNION ALL
SELECT 'Machine1',8.8,3.6,'2012-4-20 06:40:00';--select *, convert(varchar(13), date, 120) as hourP from tb;
with T as
(
select b.MID, left(a.hourP, 10) as Days, cast(right(a.hourP, 2) as int) as hour, b.AA, b.BB
from (
select  convert(varchar(13), date, 120) as hourP, max(date) as time
from tb
group by convert(varchar(13), date, 120)
  ) a
join tb b on a.time = b.date
)
select 'AA' as 字段, * 
from (select mid, days, hour, AA from T ) a
pivot
(
max(a.AA) for a.hour
in([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
   [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])
) b
union all
select 'BB' as 字段, * 
from (select mid, days, hour, BB from T ) a
pivot
(
max(a.BB) for a.hour
in([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
   [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])
) b/*
字段   mid      days          0       1       2       3       4       5       6       7       8       9       10      11      12      13      14      15      16      17      18      19      20      21      22      23
---- -------- ------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------
AA   Machine1 2012-04-20    5.60    99.99   9.00    7.20    5.60    4.20    8.80    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
BB   Machine1 2012-04-20    6.60    98.88   3.30    6.80    9.80    4.40    3.60    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL(2 行受影响)
*/

Answer 3

;with t2 as (
select *,convert(varchar(10),date,120) dd , DATEPART(HOUR, date) hh from t1
),
t3 as(
select 'AA' field,AA data,dd,hh from t2
union all select 'BB' field,BB data,dd,hh from t2 
)
select field,dd,max(case when hh=1 then data end) '1小时', max(case when hh=2 then data end) '2小时',
max(case when hh=3 then data end) '3小时', max(case when hh=4 then data end) '4小时',
max(case when hh=5 then data end) '5小时', max(case when hh=6 then data end) '6小时',
max(case when hh=7 then data end) '7小时', max(case when hh=8 then data end) '8小时',
max(case when hh=9 then data end) '9小时', max(case when hh=10 then data end) '10小时'
from t3 group by field,dd

Answer 4

谢谢各位，最感谢的是HEROWANG；问题解决了