CREATE PROCEDURE sp_businessNumberChat
@starttime datetime,
@endtime datetime
AS
declare @tempTable table(businessId int,businessname varchar(50),subbusinessid int,business_subname varchar(50))
insert into @tempTable (businessid,businessname,subbusinessid,business_subname)
select b.businessid,b.business_name,sub.subbusinessid,sub.business_subname
from business_typemain b,business_type sub
where b.businessid=sub.businessid
insert into @tempTable (businessid,businessname,subbusinessid,business_subname)
select businessid,business_name,0,' '
from business_typemain
where businessid not in (select businessid from business_type)
declare business cursor for
select businessid,subBusinessid,businessname,business_subname from @tempTable
open business
declare @tempNumber table(business_name varchar(50),business_subname varchar(50),n int)
while 1 = 1
begin
declare @bid int
declare @subid int
declare @bname varchar(50)
declare @subname varchar(50)
fetch next from business into @bid,@subid,@bname,@subname
if @@fetch_status <> 0
break
insert into @tempNumber(business_name,business_subname,n)
values(@bname,@subname,0)
insert into @tempNumber(business_name,business_subname,n)
select @bname,@subname,1
from archive
where jointime <[email protected] and jointime>[email protected] and CAST(SUBSTRING(registerid, 1, 2) AS int)[email protected] and CAST(SUBSTRING(registerid, 3, 2) AS int)[email protected]
end
close business
deallocate business
select business_name,sum(n) as num
from @tempNumber
group by business_name
GO
------解决方案--------------------
应该可以
- SQL code
CREATE FUNCTION sp_businessNumberChat( @starttime datetime,@endtime datetime)RETURNS @table table ( business_name varchar(50), num int) AS begin declare @tempTable table(businessId int,businessname varchar(50),subbusinessid int,business_subname varchar(50)) insert into @tempTable (businessid,businessname,subbusinessid,business_subname) select b.businessid,b.business_name,sub.subbusinessid,sub.business_subname from business_typemain b,business_type sub where b.businessid=sub.businessid insert into @tempTable (businessid,businessname,subbusinessid,business_subname) select businessid,business_name,0,' ' from business_typemain where businessid not in (select businessid from business_type) declare business cursor for select businessid,subBusinessid,businessname,business_subname from @tempTable open business declare @tempNumber table(business_name varchar(50),business_subname varchar(50),n int) while 1 = 1 begin declare @bid int declare @subid int declare @bname varchar(50) declare @subname varchar(50) fetch next from business into @bid,@subid,@bname,@subname if @@fetch_status <> 0 break insert into @tempNumber(business_name,business_subname,n) values(@bname,@subname,0) insert into @tempNumber(business_name,business_subname,n) select @bname,@subname,1 from archive where jointime <[email protected] and jointime>[email protected] and CAST(SUBSTRING(registerid, 1, 2) AS int)[email protected] and CAST(SUBSTRING(registerid, 3, 2) AS int)[email protected] end close business deallocate businessinsert into @table select business_name,sum(n) as num from @tempNumber group by business_namereturnend
------解决方案--------------------