高手请进，请一sql语句！！
有一表[serverlog]
表字段跟记录如下(记录是我假设的):
groupid appendtime onlinenum
1 2006-09-22 14:13:18.827 5
3 2006-09-22 14:05:18.827 5
1 2006-09-22 14:07:18.827 5
5 2006-09-22 14:08:18.827 5
4 2006-09-22 14:02:18.827 5
6 2006-09-22 14:04:18.827 5
1 2006-09-22 14:13:18.827 5
2 2006-09-22 14:13:18.827 5
6 2006-09-22 14:13:18.827 5
想要实现这种格式:
日期时间 服务组 总在线人数
年月日时 ID 人数
年月日时 ID 人数
请教sql 语句如何写
[解决办法]
select 日期时间=appendtime,服务组=groupid,总在线人数=sum(onlinenum)
from
(select groupid,convert(varchar(13),appendtime,120) as appendtime,onlinenum from serverlog) t
group by
groupid,appendtime
[解决办法]
也可以直接Group By
Select
Convert(Varchar(13),appendtime,120) As 日期时间,
groupid As 服务组,
SUM(onlinenum) As 总在线人数
From serverlog
Group By Convert(Varchar(13),appendtime,120), groupid
Order By 日期时间, 服务组
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
groupid int,
appendtime datetime,
onlinenum int
)
insert into tb(groupid,appendtime,onlinenum) values(1, '2006-09-22 14:13:18.827 ',5)
insert into tb(groupid,appendtime,onlinenum) values(3, '2006-09-22 14:05:18.827 ',5)
insert into tb(groupid,appendtime,onlinenum) values(1, '2006-09-22 14:07:18.827 ',5)
insert into tb(groupid,appendtime,onlinenum) values(5, '2006-09-22 14:08:18.827 ',5)
insert into tb(groupid,appendtime,onlinenum) values(4, '2006-09-22 14:02:18.827 ',5)
insert into tb(groupid,appendtime,onlinenum) values(6, '2006-09-22 14:04:18.827 ',5)
insert into tb(groupid,appendtime,onlinenum) values(1, '2006-09-22 14:13:18.827 ',5)
insert into tb(groupid,appendtime,onlinenum) values(2, '2006-09-22 14:13:18.827 ',5)
insert into tb(groupid,appendtime,onlinenum) values(6, '2006-09-22 14:13:18.827 ',5)
select left(convert(varchar(19),appendtime,120),13) as 日期时间 , groupid 服务组 , sum(onlinenum) as 总在线人数
from tb
group by left(convert(varchar(19),appendtime,120),13) , groupid
drop table tb
/*result
日期时间 服务组 总在线人数
------------------- ----------- -----------
2006-09-22 14 1 15
2006-09-22 14 2 5
2006-09-22 14 3 5
2006-09-22 14 4 5
2006-09-22 14 5 5
2006-09-22 14 6 10
（所影响的行数为 6 行）
*/
