有一个log表：
id userid url flow
1 100221 http://www.baidu.com/xxx/4xxx.html 120
2 100222 http://www.baidu.com/12xxx.html 180
3 100223 http://www.baidu.com/xxx/js.js 30
4 100221 http://www.goole.com/ppp/x32x.html 120
5 100221 http://www.goole.com/kkk/x3212.html 320
6 100226 http://www.163.com/xxoox/xxx.html 160
想要的结果：
id userid url flow
1 100221 www.baidu.com 120
2 100221 www.goole.com 440 (注：120+320 同一个userid 且同一个站点流量相加)
3 100222 www.baidu.com 180
4 100223 www.baidu.com 30
5 100226 www.163.com 160
--实际的log表有很多数据，且url不全是以http://www这样记录的，但大部分都是。
------解决方案--------------------
- SQL code
declare @t table (id int,userid int,url varchar(128),flow int)insert into @tselect 1,100221,'http://www.baidu.com/xxx/4xxx.html',120 union allselect 2,100222,'http://www.baidu.com/12xxx.html',180 union allselect 3,100223,'http://www.baidu.com/xxx/js.js',30 union allselect 4,100221,'http://www.goole.com/ppp/x32x.html',120 union allselect 5,100221,'http://www.goole.com/kkk/x3212.html',320 union allselect 6,100226,'http://www.163.com/xxoox/xxx.html',160 ;with t as ( select userid,left( replace(url,'http://',''),charindex('/',replace(url,'http://',''))-1) as url,flow from @t)select userid,url,sum(flow)as flow from tgroup by userid,url--------------------------(6 行受影响)userid url flow----------- ---------------------------------------------------------------------------------------------------------------- -----------100226 www.163.com 160100221 www.baidu.com 120100222 www.baidu.com 180100223 www.baidu.com 30100221 www.goole.com 440(5 行受影响)
------解决方案--------------------
- SQL code
create table tb ( id int identity(1,1),userid bigint,url varchar(128),flow int)insert tb select 100221,'http://www.baidu.com/xxx/4xxx.html',120 union allselect 100222 ,'http://www.baidu.com/12xxx.html',180 union allselect 100223 ,'http://www.baidu.com/xxx/js.js',30 union allselect 100221 ,'http://www.goole.com/ppp/x32x.html',120 union allselect 100221 ,'http://www.goole.com/kkk/x3212.html',320 union allselect 100226 ,'http://www.163.com/xxoox/xxx.html',160SELECT ROW_NUMBER() OVER(ORDER BY GETDATE())RN,USERID,URL,SUM(FLOW)FLOWFROM (select USERID,SUBSTRING(url,CHARINDEX('W',URL,0),CHARINDEX('M',URL,0)-CHARINDEX('W',URL,0)+1)URL,FLOWFROM tb )AS AGROUP BY USERID,URLORDER BY USERID /*RN USERID URL FLOW1 100221 www.baidu.com 1202 100221 www.goole.com 4403 100222 www.baidu.com 1804 100223 www.baidu.com 305 100226 www.163.com 160*/