---------------------------------
--  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' 时间的起始点，这里用的是固定的日期，

------解决方案--------------------

借用一下 HEROWANG 的测试数据。
SQL code
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 'Ma