sql 存储过程的学习 和实例



CREATE procedure createBookmarkReport
as

begin
declare @date nvarchar(8)
declare @table nvarchar(2000)

set @date=convert(nvarchar,dateadd(dd,-1,getdate()),112)
set @table = 'wapbase.wap_bookmark_' + convert(nvarchar,year(getdate()))

--统计格式
--序列号、日期、访问人数、访问次数、书签ID、书签名称、书签所有者
if not exists (select * from dbo.sysobjects where id = object_id( @table ) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
exec (
'create table ' + @table + '(
seqid int not null identity(1,1) primary key,
id int not null,
title nvarchar(50) not null,
own nvarchar(50),
views int not null default(0),
visits int not null default(0),
[date] nvarchar(8) not null
)'
)
end

if year(@date) < year(getdate())
set @table = 'wapbase.wap_bookmark_' + convert(nvarchar,year(@date))

declare @prefix nvarchar(100)
declare @subfix nvarchar(100)
declare @sql nvarchar(2000)

set @prefix = 'http://wap.lenovomobile.com/bookmark.do?id='
set @subfix = '&'

set @sql = '
insert into ' + @table + '(id,title,own,views,visits,[date])
select b.seqid, b.title, b.own, count(*) views, count(distinct mid) visits,''' + @date + '''
from wap_lenovo_bookmark b left join (
select substring(url,len(''' + @prefix + ''')+1,len(url)-len(''' + @prefix +''')) id, ua, mid
'
set @sql = @sql + ' from wapbase.wap_log_' + @date
+ ' where charindex(''' + @prefix + ''',url,1)>0 and charindex(''' + @subfix + ''',url,1) = 0'

+ ' union all '

+ ' select substring(url,len(''' + @prefix + ''')+1,charindex(''' + @subfix + ''',url,1)-len(''' + @prefix + ''')-1) id, ua, mid'
+ ' from wapbase.wap_log_' + @date
+ ' where charindex(''' + @prefix + ''',url,1)>0 and charindex(''' + @subfix + ''',url,1) > 0'
+ ') a on b.seqid = a.id'
+ ' group by b.seqid, b.title, b.own'
+ ' order by b.seqid desc'

exec(@sql)


end


GO
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值