刚学存储过程,遇到下面的问题:create procedure RPT(@user_id varchar(30),@date1 date,@date2 date,@product_id varchar(100))
as
select
date_drv.date1 as user_time,
sum(pv) as pv,
sum(uv) as uv
from date_drv
left join reprot a
ON date_drv.date1=a.user_time and a.user_id=@user_id
where date_drv.date1 between @date1 and @date2
and product_id=@product_id
group by date_drv.date1,a.product_id order by 1;
在上面的存储过程中,user_time 有时候需要传入date_drv.date1 ,有时候需要传入date_drv.month1 ,这样的存储过程怎么写呢?谢谢!
as
select
date_drv.date1 as user_time,
sum(pv) as pv,
sum(uv) as uv
from date_drv
left join reprot a
ON date_drv.date1=a.user_time and a.user_id=@user_id
where date_drv.date1 between @date1 and @date2
and product_id=@product_id
group by date_drv.date1,a.product_id order by 1;
在上面的存储过程中,user_time 有时候需要传入date_drv.date1 ,有时候需要传入date_drv.month1 ,这样的存储过程怎么写呢?谢谢!
create procedure RPT
(@user_id varchar(30),@date1 date,@date2 date,@product_id varchar(100),@flag int)
as
select
(case when @flag = 1 then date_drv.date1 else date_drv.month1 end) as user_time,
sum(pv) as pv,
sum(uv) as uv
from date_drv
left join reprot a
ON (case when @flag = 1 then date_drv.date1 else date_drv.month1 end)=a.user_time
and a.user_id=@user_id
where (case when @flag = 1 then date_drv.date1 else date_drv.month1 end) between @date1 and @date2
and product_id=@product_id
group by (case when @flag = 1 then date_drv.date1 else date_drv.month1 end),a.product_id order by 1;貌似这样也可以!
如果在外面加 if...begin ...end
else if...begin...end
这样可以得到正确结果。可是如果参数传入很多的话,比如既有按月(month1),又有按日(date1),又有按周(week1)等等进行数据汇总的话,相当于同一段程序写了很多遍,代码太长了...不知道有没有好的解决方法?