creatjavareport

最新推荐文章于 2022-02-25 23:47:59 发布

wangqiaowqo

最新推荐文章于 2022-02-25 23:47:59 发布

阅读量72

点赞数

CC 4.0 BY-SA版权

文章标签： WAP SQL CMS C C++

本文链接：https://blog.youkuaiyun.com/wangqiaowqo/article/details/83553345

本文介绍了一个用于创建Java应用报告统计表的SQL存储过程。该过程通过复杂的SQL语句实现，涉及多表联接、子查询及条件筛选等操作，最终生成包含应用ID、名称、下载次数等关键信息的统计数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE procedure createJavaReport
as

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

set @date=convert(nvarchar,dateadd(dd,-1,getdate()),112)
set @table = 'cmsbase.wap_java_download_report_log'

--统计格式
--序列号、日期、访问人数、访问次数、书签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,
javaid int not null default(0),
title nvarchar(100) not null default('' ''),
jads int not null default(0),
jars int not null default(0),
[date] nvarchar(8),
createddate datetime not null default(getdate())
)'
)
end

declare @sql nvarchar(2000)

set @sql = 'insert into ' + @table + '(javaid, title, jads, jars, [date]) '
set @sql = @sql + ' select d.seqid, d.title, c.jads, c.jars, ''' + @date + ''''
set @sql = @sql + ' from (
select a.seqid, isnull(b.jads,0) jads, isnull(a.jars,0) jars from
(
select isnull(b.seqid,0) seqid, count(*) jars
from wap_log_' + @date + ' a left join wap_cms_java_detail b on substring(url,charindex(''jad='',url)+4,charindex(''.jar'',url)-charindex(''jad='',url))=b.jar
and substring(url, charindex(''id='', url)+3, charindex(''&jad='', url)-charindex(''id='', url)-3)=b.seqid
where charindex(''.jar'',url)>0
group by b.seqid
) a left join (
select isnull(b.seqid,0) seqid, count(*) jads
from wap_log_' + @date + ' a left join wap_cms_java_detail b on substring(url,charindex(''jad='',url)+4,charindex(''.jad'',url)-charindex(''jad='',url))=b.jad
and substring(url, charindex(''id='', url)+3, charindex(''&jad='', url)-charindex(''id='', url)-3)=b.seqid
where charindex(''.jad'',url)>0
group by b.seqid
) b
on a.seqid = b.seqid
) c left join wap_cms_java_detail d on d.seqid=c.seqid where d.seqid is not null '
--print @sql

exec(@sql)
end
GO