表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 pageviewsfrom table_01group by
frompage,pageid with rolluphaving
grouping(frompage)=0
)a where pageviews not like '%.%'select * from #tt
order by cast(pageviews as int) descdrop table #tt
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 pageviewsfrom table_01group by
frompage,pageid with rolluphaving
grouping(frompage)=0
)a where pageviews not like '%.%'select * from #tt
order by cast(pageviews as int) descdrop table #tt
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
不过
这个问题没有那么简单哦,
就是得到如下结果:
frompage pageviews sessionids
----------------------------------------------
www.163.com 28139 ?
www.164.com 5902 ?
www.165.com 5309 ?
select min(sessionId),frompage, sum(pageid) as pageviews
from table1
group by frompage
order by sum(pageid) desc
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
table1中这两个字段要一一对应。