【关于按小时统计SQL】请各位帮帮忙,该怎么处理
【关于按小时统计SQL】请各位帮帮忙
表结构如下:
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 .... ....
某个小时内可能存在多条记录,只要查询最新日期的那一条就行
------解决方案--------------------
楼主,请再详细描述一下你的结果,比如 AA,在“0-1小时内”的值为什么是5.6,而在“1-2小时内”的值是99.99
------解决方案--------------------
表结构如下:
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 .... ....
某个小时内可能存在多条记录,只要查询最新日期的那一条就行
------解决方案--------------------
楼主,请再详细描述一下你的结果,比如 AA,在“0-1小时内”的值为什么是5.6,而在“1-2小时内”的值是99.99
------解决方案--------------------
- SQL code
--------------------------------- -- 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 '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 行受影响) */