求一条高效的SQL语句
有一个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这样记录的，但大部分都是。
[解决办法]
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 行受影响)
[解决办法]
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*/
[解决办法]
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2012-07-13 10:38:26-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[log]if object_id('[log]') is not null drop table [log]go create table [log]([id] int,[userid] int,[url] varchar(35),[flow] int)insert [log]select 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--------------开始查询---------------------------- select case when charindex('http://',url)>0 then substring(url,charindex('//',url),charindex('m/',url)-charindex('//',url)) else url end from [log]select userid, url=case when charindex('http://',url)>0 then substring(url,charindex('//',url)+2,charindex('m/',url)-charindex('//',url)-1) else url end, sum(flow) as flow from [log]group by userid,case when charindex('http://',url)>0 then substring(url,charindex('//',url)+2,charindex('m/',url)-charindex('//',url)-1) else url end----------------结果----------------------------/*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 行受影响) */
[解决办法]