日期:2014-05-19  浏览次数:20368 次

帮忙看看一小问题,分太少,对不住大家了
表tbl_accessstat_onday,数据库这个样子
id               weburl     totaltime                               inputtime
1 新浪 45.80 2007-06-22   00:00:00.000
2 搜狐 9.20 2007-06-22   00:00:00.000
3 新浪 35.20 2007-06-21   00:00:00.000
4 搜狐 12.10 2007-06-21   00:00:00.000
5 新浪 41.30 2007-06-20   00:00:00.000
6 搜狐 11.70 2007-06-20   00:00:00.000
7 网易 12.70 2007-06-20   00:00:00.000
我想用SQL语句给倒腾成这样的结构
                              新浪             搜狐           网易
2007-06-20           41.30           11.70         12.70
2007-06-21           35.20           12.10        
2007-06-22           45.80           9.20

注意:新浪,搜狐,网易,这些网址是变化的,明天可能又会有百度等,所以写SQL时不能写的死了,那样修改会很麻烦
我这么写

select   *   from  
(select   weburl,totaltime   as   atotaltime,inputtime   from   tbl_accessstat_onday   where   weburl= '新浪 ')   as   sinatable,
(select   weburl,totaltime,inputtime   from   tbl_accessstat_onday   where   weburl= '搜狐 ')   as   sohutable,
(select   weburl,totaltime,inputtime   from   tbl_accessstat_onday   where   weburl= '网易 ')   as   163table,
where   sinatable.inputtime=sohutable.inputtime   and   sohutable=163.table

这样虽然也能出来,大概的达到这种效果,但是太麻烦,以后变化了太麻烦,而且这样出来的结果字段名字也一样,做程序不好分辨

高手帮忙!!用SQL也行,也可以给东西倒腾到一个新的数据表中,总之能达到那样的效果就成了
没分了,真不好意思,大家就辛苦点,义务帮我一下把,谢谢了先


------解决方案--------------------
create table tbl_accessstat_onday
(
id int,
weburl varchar(50),
totaltime float,
inputtime datetime
)
go
insert into tbl_accessstat_onday
select 1, '新浪 ',45.80, '2007-06-22 00:00:00.000 ' union all
select 2, '搜狐 ',9.20 , '2007-06-22 00:00:00.000 ' union all
select 3, '新浪 ',35.20, '2007-06-21 00:00:00.000 ' union all
select 4, '搜狐 ',12.10, '2007-06-21 00:00:00.000 ' union all
select 5, '新浪 ',41.30, '2007-06-20 00:00:00.000 ' union all
select 6, '搜狐 ',11.70, '2007-06-20 00:00:00.000 ' union all
select 7, '网易 ',12.70, '2007-06-20 00:00:00.000 '
go
declare @sql varchar(500)
set @sql= 'select inputtime '
select @sql=@sql+ ',sum(case weburl when ' ' '+weburl+ ' ' ' then totaltime else 0 end) as '+weburl from
(select distinct weburl from tbl_accessstat_onday) as a
set @sql=@sql+ ' from tbl_accessstat_onday group by inputtime '
exec( @sql)
--结果
inputtime 搜狐 网易 新浪
----------------------- ---------------------- ---------------------- ----------------------
2007-06-20 00:00:00.000 11.7 12.7 41.3
2007-06-21 00:00:00.000 12.1 0 35.2
2007-06-22 00:00:00.000 9.2 0 45.8

(3 行受影响)
------解决方案--------------------
select convert(char(10),inputtime,