34,596
社区成员
发帖
与我相关
我的任务
分享
if object_id('tb')is not null drop table tb
go
create table tb(
id int identity(1,1) primary key, --ID
VisitorID varchar(8), --访客标识
WebMasterId varchar(8), --站长标识
WebSiteId varchar(8), --站点标识
AdvertiserId varchar(8), --广告主标识
AdSeriesId varchar(8), --系列标识
AdGroupId varchar(8), --组标识
AdId varchar(8), --广告标识
AdKeyWordId varchar(8), --关键字标识
MarkId varchar(8), --图钉标识
ImageId varchar(8), --图片标识
[Time] datetime, --发生时间
[Type] nvarchar(10), --类型(点击/展示)
Validity bit, --有效性
Price numeric(6,2) --价值
)
insert tb (AdvertiserId,Time,Type,Price)
select 1,getdate()-1,'点击',2.3 union all
select 1,getdate(),'点击',2.3 union all
select 1,getdate(),'点击',2.3 union all
select 1,getdate()-1,'展示',2.3 union all
select 1,getdate()-2,'点击',2.3 union all
select 1,getdate()-4,'点击',2.3 union all
select 1,getdate()-5,'点击',2.3 union all
select 1,getdate()-1,'点击',2.3
go
if object_id('p_test')is not null drop proc p_test
go
create proc p_test @starttime datetime,@endtime datetime,@type nvarchar(4),@uid varchar(8),@where varchar(8000)
as
begin
declare @sql varchar(8000),@w varchar(1000),@g varchar(1000)
--1 构建select部分
if @type='统计' or @type is null
begin
set @sql='select isnull(sum(case when type=''点击'' then 1 else 0 end),0) 点此次数,
isnull(sum(case when type=''展示'' then 1 else 0 end),0) 展示次数,
isnull(sum(Price),0.00) 金额,'''+convert(varchar(10),@starttime,120)+'~'+convert(varchar(10),@endtime,120)+'''as [Time]
from tb where 1=1 '
set @w=''
if @starttime<>'' and @starttime is not null
set @w=' and datediff(d,'''+convert(varchar(10),@starttime,120)+''',[Time])>=0 '
if @endtime<>'' and @starttime is not null
set @w=@w+' and datediff(d,'''+convert(varchar(10),@endtime,120)+''',[Time])<=0 '
if @uid<>'' and @uid is not null
set @w=@w+' and AdvertiserId='''+@uid+''''
if @where<>'' and @where is not null
set @w=@w+' and '+@where
exec(@sql+@w)
end
if @type='明细'
begin
set @sql='select isnull(sum(case when t.type=''点击'' then 1 else 0 end),0) 点此次数,
isnull(sum(case when t.type=''展示'' then 1 else 0 end),0) 展示次数,
isnull(sum(Price),0.00) 金额,dateadd(day,n.number,'''+convert(varchar(10),@starttime,120)+''') Time
from master..spt_values n left join tb t on datediff(day,dateadd(day,n.number,'''+convert(varchar(10),@starttime,120)+'''),[time])=0
where n.type=''p'' and n.number between 0 and datediff(day,'''+convert(varchar(10),@starttime,120)+''','''+convert(varchar(10),@endtime,120)+''')'
set @g=' group by dateadd(day,n.number,'''+convert(varchar(10),@starttime,120)+''')'
exec(@sql+@w+@g)
end
end
go
p_test '2010-6-19','2010-6-20','统计','',''
go
p_test '2010-5-1','2010-12-1','明细','',''
/*
(所影响的行数为 8 行)
点此次数 展示次数 金额 Time
----------- ----------- ---------------------------------------- ---------------------
4 1 11.50 2010-06-19~2010-06-20
(所影响的行数为 1 行)
点此次数 展示次数 金额 Time
----------- ----------- ---------------------------------------- ------------------------------------------------------
0 0 .00 2010-05-01 00:00:00.000
0 0 .00 2010-05-02 00:00:00.000
0 0 .00 2010-05-03 00:00:00.000
0 0 .00 2010-05-04 00:00:00.000
0 0 .00 2010-05-05 00:00:00.000
0 0 .00 2010-05-06 00:00:00.000
0 0 .00 2010-05-07 00:00:00.000
0 0 .00 2010-05-08 00:00:00.000
......
0 0 .00 2010-06-03 00:00:00.000
0 0 .00 2010-06-04 00:00:00.000
0 0 .00 2010-06-05 00:00:00.000
0 0 .00 2010-06-06 00:00:00.000
0 0 .00 2010-06-07 00:00:00.000
0 0 .00 2010-06-08 00:00:00.000
0 0 .00 2010-06-09 00:00:00.000
0 0 .00 2010-06-10 00:00:00.000
0 0 .00 2010-06-11 00:00:00.000
0 0 .00 2010-06-12 00:00:00.000
0 0 .00 2010-06-13 00:00:00.000
0 0 .00 2010-06-14 00:00:00.000
1 0 2.30 2010-06-15 00:00:00.000
1 0 2.30 2010-06-16 00:00:00.000
0 0 .00 2010-06-17 00:00:00.000
1 0 2.30 2010-06-18 00:00:00.000
2 1 6.90 2010-06-19 00:00:00.000
2 0 4.60 2010-06-20 00:00:00.000
0 0 .00 2010-06-21 00:00:00.000
0 0 .00 2010-06-22 00:00:00.000
0 0 .00 2010-06-23 00:00:00.000
0 0 .00 2010-06-24 00:00:00.000
0 0 .00 2010-06-25 00:00:00.000
0 0 .00 2010-06-26 00:00:00.000
......
0 0 .00 2010-11-21 00:00:00.000
0 0 .00 2010-11-22 00:00:00.000
0 0 .00 2010-11-23 00:00:00.000
0 0 .00 2010-11-24 00:00:00.000
0 0 .00 2010-11-25 00:00:00.000
0 0 .00 2010-11-26 00:00:00.000
0 0 .00 2010-11-27 00:00:00.000
0 0 .00 2010-11-28 00:00:00.000
0 0 .00 2010-11-29 00:00:00.000
0 0 .00 2010-11-30 00:00:00.000
0 0 .00 2010-12-01 00:00:00.000
(所影响的行数为 215 行)
警告: 聚合或其他 SET 操作消除了空值。
*/
if object_id('tb')is not null drop table tb
go
create table tb(
id int identity(1,1) primary key, --ID
VisitorID varchar(8), --访客标识
WebMasterId varchar(8), --站长标识
WebSiteId varchar(8), --站点标识
AdvertiserId varchar(8), --广告主标识
AdSeriesId varchar(8), --系列标识
AdGroupId varchar(8), --组标识
AdId varchar(8), --广告标识
AdKeyWordId varchar(8), --关键字标识
MarkId varchar(8), --图钉标识
ImageId varchar(8), --图片标识
[Time] datetime, --发生时间
[Type] nvarchar(10), --类型(点击/展示)
Validity bit, --有效性
Price numeric(6,2) --价值
)
insert tb (AdvertiserId,Time,Type,Price)
select 1,getdate()-1,'点击',2.3 union all
select 1,getdate(),'点击',2.3 union all
select 1,getdate(),'点击',2.3 union all
select 1,getdate()-1,'展示',2.3 union all
select 1,getdate()-2,'点击',2.3 union all
select 1,getdate()-4,'点击',2.3 union all
select 1,getdate()-5,'点击',2.3 union all
select 1,getdate()-1,'点击',2.3
go
if object_id('p_test')is not null drop proc p_test
go
create proc p_test @starttime datetime,@endtime datetime,@type nvarchar(4),@uid varchar(8),@where varchar(8000)
as
begin
declare @sql varchar(8000),@w varchar(1000),@g varchar(1000)
--1 构建select部分
if @type='统计' or @type is null
set @sql='select isnull(sum(case when type=''点击'' then 1 else 0 end),0) 点此次数,
isnull(sum(case when type=''展示'' then 1 else 0 end),0) 展示次数,
isnull(sum(Price),0.00) 金额,'''+convert(varchar(10),@starttime,120)+'~'+convert(varchar(10),@endtime,120)+'''as [Time]
from tb where 1=1 '
if @type='明细'
set @sql='select isnull(sum(case when type=''点击'' then 1 else 0 end),0) 点此次数,
isnull(sum(case when type=''展示'' then 1 else 0 end),0) 展示次数,
isnull(sum(Price),0.00) 金额,convert(varchar(10),[Time],120) Time
from tb where 1=1 '
--2 构建where部分
set @w=''
if @starttime<>'' and @starttime is not null
set @w=' and datediff(d,'''+convert(varchar(10),@starttime,120)+''',[Time])>=0 '
if @endtime<>'' and @starttime is not null
set @w=@w+' and datediff(d,'''+convert(varchar(10),@endtime,120)+''',[Time])<=0 '
if @uid<>'' and @uid is not null
set @w=@w+' and AdvertiserId='''+@uid+''''
if @where<>'' and @where is not null
set @w=@w+' and '+@where
--3 构建group by
set @g=''
if @type='明细'
set @g=' group by convert(varchar(10),[Time],120)'
exec(@sql+@w+@g)
end
go
p_test '2010-6-19','2010-6-20','统计','',''
go
p_test '','','明细','',''
/*
(所影响的行数为 8 行)
点此次数 展示次数 金额 Time
----------- ----------- ---------------------------------------- ---------------------
4 1 11.50 2010-06-19~2010-06-20
(所影响的行数为 1 行)
点此次数 展示次数 金额 Time
----------- ----------- ---------------------------------------- ----------
1 0 2.30 2010-06-15
1 0 2.30 2010-06-16
1 0 2.30 2010-06-18
2 1 6.90 2010-06-19
2 0 4.60 2010-06-20
(所影响的行数为 5 行)
*/
if object_id('tb')is not null drop table tb
go
create table tb(
id int identity(1,1) primary key, --ID
VisitorID varchar(8), --访客标识
WebMasterId varchar(8), --站长标识
WebSiteId varchar(8), --站点标识
AdvertiserId varchar(8), --广告主标识
AdSeriesId varchar(8), --系列标识
AdGroupId varchar(8), --组标识
AdId varchar(8), --广告标识
AdKeyWordId varchar(8), --关键字标识
MarkId varchar(8), --图钉标识
ImageId varchar(8), --图片标识
[Time] datetime, --发生时间
[Type] nvarchar(10), --类型(点击/展示)
Validity bit, --有效性
Price numeric(6,2) --价值
)
insert tb (AdvertiserId,Time,Type,Price)
select 1,getdate()-1,'点击',2.3 union all
select 1,getdate(),'点击',2.3 union all
select 1,getdate(),'点击',2.3 union all
select 1,getdate()-1,'展示',2.3 union all
select 1,getdate()-2,'点击',2.3 union all
select 1,getdate()-4,'点击',2.3 union all
select 1,getdate()-5,'点击',2.3 union all
select 1,getdate()-1,'点击',2.3
go
if object_id('p_test')is not null drop proc p_test
go
create proc p_test @starttime datetime,@endtime datetime,@type nvarchar(4),@uid varchar(8),@where varchar(8000)
as
begin
declare @sql varchar(8000),@w varchar(1000),@g varchar(1000)
--1 构建select部分
if @type='统计' or @type is null
set @sql='select isnull(sum(case when type=''点击'' then 1 else 0 end),0) 点此次数,
isnull(sum(case when type=''展示'' then 1 else 0 end),0) 展示次数,
isnull(sum(Price),0.00) 金额
from tb where 1=1 '
if @type='明细'
set @sql='select isnull(sum(case when type=''点击'' then 1 else 0 end),0) 点此次数,
isnull(sum(case when type=''展示'' then 1 else 0 end),0) 展示次数,
isnull(sum(Price),0.00) 金额,convert(varchar(10),[Time],120) Time
from tb where 1=1 '
--2 构建where部分
set @w=''
if @starttime<>'' and @starttime is not null
set @w=' and datediff(d,'''+convert(varchar(10),@starttime,120)+''',[Time])>=0 '
if @endtime<>'' and @starttime is not null
set @w=@w+' and datediff(d,'''+convert(varchar(10),@endtime,120)+''',[Time])<=0 '
if @uid<>'' and @uid is not null
set @w=@w+' and AdvertiserId='''+@uid+''''
if @where<>'' and @where is not null
set @w=@w+' and '+@where
--3 构建group by
set @g=''
if @type='明细'
set @g=' group by convert(varchar(10),[Time],120)'
exec(@sql+@w+@g)
end
go
p_test '2010-6-19','2010-6-20','统计','',''
go
p_test '','','明细','',''
/*
(所影响的行数为 8 行)
点此次数 展示次数 金额
----------- ----------- ----------------------------------------
4 1 11.50
(所影响的行数为 1 行)
点此次数 展示次数 金额
----------- ----------- ----------------------------------------
1 0 2.30
1 0 2.30
1 0 2.30
2 1 6.90
2 0 4.60
(所影响的行数为 5 行)
*/
ding