问:
表结构如下
time ne RegUser OpenUserCount Sysrate
2006-10-13 15:00WHMSC141.61288254 256915
2006-10-13 15:00WHMSC236.12255287 226457
2006-10-13 15:00WHMSC324.34191782 169963
2006-10-13 15:00WHMSC418.04150165 133202
2006-10-13 15:00WHMSC545.9322620 287878
2006-10-13 15:00WHMSC636.5239202 214127
2006-10-13 15:00WHMSC746.81331721 297042
2006-10-13 15:00WHMSC846.38240098 215498
2006-10-13 15:00WHMSC951.58323802 292248
2006-10-13 15:00WHMSCA136.81304640 271097
2006-10-13 15:00WHMSCB36.79316382 278293
2006-10-13 15:00WHMSCC38.69310090 277396
2006-10-13 15:00WHMSCD22.26206749 182311
现在要做成这样
time ne 字段 值
2006-10-13 15:00WHMSC1 RegUser 41.61
2006-10-13 15:00WHMSC1 OpenUserCount 288254
2006-10-13 15:00 WHMSC1 Sysrate 256915
... 中间省略 ... ... ... ...
2006-10-13 15:00WHMSCD RegUser 22.26
2006-10-13 15:00WHMSCD OpenUserCount 206749
2006-10-13 15:00 WHMSCD Sysrate 182311
----到了下一个小时还是这样,按time,ne分组,如
time ne 字段 值
2006-10-13 16:00WHMSC1 RegUser 41.61
2006-10-13 16:00WHMSC1 OpenUserCount 288254
2006-10-13 16:00 WHMSC1 Sysrate 256915
... 中间省略 ... ... ... ...
2006-10-13 16:00WHMSCD RegUser 22.26
2006-10-13 16:00WHMSCD OpenUserCount 206749
2006-10-13 16:00 WHMSCD Sysrate 182311
----------------------------------------------------------------------
答:
if object_id('pub..tab') is not null
drop table tab
go
create table tab
(
time datetime,
ne varchar(10),
ResUser varchar(10),
openusercount varchar(10),
sysrate varchar(10)
)
insert into tab(time,ne,ResUser,openusercount,sysrate) values('2006-10-13 15:00','WHMSC1','41.61','288254','256915')
insert into tab(time,ne,ResUser,openusercount,sysrate) values('2006-10-13 15:00','WHMSC2','36.12','255287','226457')
insert into tab(time,ne,ResUser,openusercount,sysrate) values('2006-10-13 16:00','WHMSC1','41.62','288256','256916')
insert into tab(time,ne,ResUser,openusercount,sysrate) values('2006-10-13 16:00','WHMSC2','36.16','255286','226456')
select convert(varchar(16),time,120) as time,ne ,'ResUser' as 字段,ResUser as 值 from tab union
select convert(varchar(16),time,120) as time,ne,'openusercount' as 字段,openusercount as 值 from tab union
select convert(varchar(16),time,120) as time,ne,'sysrate' as 字段,sysrate as 值 from tab
order by time,ne
drop table tab
--结果
time ne 字段 值
---------------- ---------- ------------- ----------
2006-10-13 15:00 WHMSC1 openusercount 288254
2006-10-13 15:00 WHMSC1 ResUser 41.61
2006-10-13 15:00 WHMSC1 sysrate 256915
2006-10-13 15:00 WHMSC2 openusercount 255287
2006-10-13 15:00 WHMSC2 ResUser 36.12
2006-10-13 15:00 WHMSC2 sysrate 226457
2006-10-13 16:00 WHMSC1 openusercount 288256
2006-10-13 16:00 WHMSC1 ResUser 41.62
2006-10-13 16:00 WHMSC1 sysrate 256916
2006-10-13 16:00 WHMSC2 openusercount 255286
2006-10-13 16:00 WHMSC2 ResUser 36.16
2006-10-13 16:00 WHMSC2 sysrate 226456
(所影响的行数为 12 行)