求解一SQL难题,高手请进!!!

wilsonsql 2007-02-02 02:47:50
表table1中有字段:sessionid,pageid,frompage;
sessionid, pageid, frompage
-----------------------------------
2jwgez30qo4 3424 www.164.com
5hwgez356o4 2345 www.165.com
......
下面的语句实现:
将frompage相同的记录汇总,得到结果后将结果按递减进行排序。
结果如:
frompage pageviews
-----------------------------
www.163.com 28139
www.164.com 5902
www.165.com 5309

现在我想在以上基础上将sessionid的参数考虑进来:
就是得到如下结果:
frompage pageviews sessionids
----------------------------------------------
www.163.com 28139 ?
www.164.com 5902 ?
www.165.com 5309 ?

不知道应该怎么处理?请各位高手指导,谢谢!

---------------------------------------------------------------------------
select * into #tt from (select
frompage,(case when pageid is null then rtrim(count(*))else frompage end) as pageviews

from table_01

group by
frompage,pageid with rollup

having
grouping(frompage)=0
)a

where pageviews not like '%.%'

select * from #tt
order by cast(pageviews as int) desc

drop table #tt
...全文
254 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
rookie_one 2007-02-02
个人觉得,lz如果想把sessionid加进来,而且保证frompage不重复,那必须有个前提:
table1中这两个字段要一一对应。
回复
rookie_one 2007-02-02

select t1.*,t2.sessionid
from
(select frompage,sum(pageid) pageid
from table1
group by frompage) t1 inner join table1 t2
on t1.frompage=t2.frompage
order by t1.pageid DESC
回复
wilsonsql 2007-02-02
估计是不行了
回复
zsl5305256 2007-02-02

select min(sessionId),frompage, sum(pageid) as pageviews
from table1
group by frompage
order by sum(pageid) desc
回复
silvanus 2007-02-02
这要是要聚合的,要写存储过程或function,SQL是没有办法解决的
回复
wilsonsql 2007-02-02
谢谢楼上的大老
不过
这个问题没有那么简单哦,
就是得到如下结果:
frompage pageviews sessionids
----------------------------------------------
www.163.com 28139 ?
www.164.com 5902 ?
www.165.com 5309 ?
回复
gzhughie 2007-02-02
select frompage, sum(pageid) as pageviews
from table1
group by frompage
order by sum(pageid) desc



select frompage, sum(pageid) as pageviews, '?' as sessionids
from table1
group by frompage
order by sum(pageid) desc
回复
wilsonsql 2007-02-02
各位大侠,谢谢啊!!!
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2007-02-02 02:47
社区公告
暂无公告