这个存储过程怎么有两个选择SQL的呢?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[proc_getActionConsumesByUserAndTime]
@begintime datetime,
@endtime datetime,
@username varchar(20),
@begin int,
@end int
as begin
declare @com as varchar(max)
set @com='
--if object_id(''tempdb..#actions'') is not null
--drop table #actions
--
--create table #actions
--(actionid bigint,
--nums int,
--shopid bigint,
--shopname varchar(20),
--action varchar(10),
--totalmoney decimal(16,2),
--consumerTime datetime,
--cemeteryname varchar(30),
--subuserid int,
--subusername varchar(20),
--currentmoney decimal(16,2)
--)
if object_id(''tempdb..#buynotes'') is not null
drop table #buynotes
create table #buynotes
(actionid bigint,
nums int,
shopid bigint,
shopname varchar(30),
action varchar(10),
totalmoney decimal(16,2),
consumerTime datetime,
cemeteryname varchar(30),
subuserid int,
subusername varchar(20),
currentmoney decimal(16,2),
unit varchar(10)
)
if object_id(''tempdb..#combine'') is not null
drop table #combine
create table #combine
(
actionid bigint,
nums int,
shopid bigint,
shopname varchar(30),
action varchar(10),
totalmoney decimal(16,2),
consumerTime datetime,
cemeteryname varchar(30),
subuserid int,
subusername varchar(20),
currentmoney decimal(16,2),
unit varchar(10)
)
--insert into #actions
--select actionconsumecc.actionid, sum(actionconsumecc.nums) nums,actionconsumecc.shopid,actionconsumecc.shopname,
--''祭祀'',actionconsumec.totalmoney,actionconsumec.consumerTime,dbo.cemeterys.cemeteryname,
--actionconsumec.subuserid,actionconsumec.subusername from actionconsumecc inner
-- join actionconsumec on actionconsumec.actionid=actionconsumecc.actionid inner join
--dbo.cemeterys on actionconsumec.cemeteryid=dbo.cemeterys.cemeterysid and actionconsumec.hillid=dbo.cemeterys.hill
--where actionconsumec.consumerTime>='''+convert(varchar(10),@begintime,121)+''' and
--actionconsumec.consumerTime<'''+convert(varchar(10),dateAdd(dd,1,@endtime),121)+''' and
--actionconsumec.username='''+Cast(@username as varchar(20))+'''
--group by actionconsumecc.actionid,actionconsumecc.shopid,actionconsumecc.shopname,actionconsumec.totalmoney,
--actionconsumec.consumerTime,dbo.cemeterys.cemeteryname,actionconsumec.subuserid,actionconsumec.subusername
insert into #buynotes
select -1 actionid,nums,shopid,shopname,buytype,buyprice totalmoney,buytime,dbo.cemeterys.cemeteryname,
buynote.subuserId,heavensubuser.subusername,currentmoney,buynote.unit from buynote join heavenusers on heavenusers.uid=buynote.userid
inner join heaven_bbs.dbo.heaven_users b on b.uid=heavenusers.uid left outer join
dbo.cemeterys on dbo.cemeterys.cemeterysid=buynote.tartgetid left outer join
heavensubuser on heavenusers.uid=heavensubuser.userid and buynote.subuserid>0 where
b.username='''+@username+''' and buytime>='''+convert(varchar(10),@begintime,121)+'''
and buytime<'''+convert(varchar(10),dateadd(dd,1,@endtime),121)+'''
insert into #combine
--select * from #actions
--union
select * from #buynotes
select count(*) from #combine
select * from (select *,row_number() over(order by consumerTime desc) cid from #combine)
moneying where moneying.cid>'''+Cast(@begin as varchar(10))+''' and
moneying.cid<='''+cast(@end as varchar(10))+''' order by consumerTime desc,action desc
'
exec(@com)
end
最近看公司的代码 好晕呀。
创建临时表#buynotes和#combine ,然后插入数据。 但返回的怎么读取的呢,这个有两个 都不知道怎么用的呢
还这个不明白 --where actionconsumec 前面加上 --是不是注释掉的意思呢?? 请高手帮忙谢谢/