create function getstr(@content varchar(100))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+',zone-'+cast(zone as varchar(10))+'-'+cast([sum] as varchar(10)) from 表 where car_no=@content
set @str=right(@str,len(@str)-1)
return @str
end
go
select car_no,dbo.getstr(car_no) zone,sum([sum]) total from 表 group by car_no
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+',zone-'+cast(zone as varchar(10))+'-'+cast([sum] as varchar(10)) from 表 where car_no=@content
set @str=right(@str,len(@str)-1)
return @str
end
go
select car_no,dbo.getstr(car_no) zone,sum([sum]) total from 表 group by car_no
多谢!从来没用过function,该怎么调用啊?见笑了!
CREATE view viewZonebyCarno as
select car_no,pu_disp_zone,count(pu_disp_zone)as num from operator_archive where car_no is not null and rtrim(ltrim(car_no))<>'' and status_flag in ( 'D' ,'K' ,'F' ,'N')
group by car_no,pu_disp_zone
是不是因为这个原因慢啊?
select car_no,pu_disp_zone,count(pu_disp_zone)as num from operator_archive where car_no is not null and rtrim(ltrim(car_no))<>'' and status_flag in ( 'D' ,'K' ,'F' ,'N')
group by car_no,pu_disp_zone
order by car_no
create function getstr(@Car_no varchar(100),@zone varchar(100))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+',zone-'+cast(zone as varchar(10))+'-'+cast(Count(*) as varchar(10))
from operator_archive
where car_no=@content and zone=@zone
and car_no is not null and rtrim(ltrim(car_no))<>'' and status_flag in ( 'D' ,'K' ,'F' ,'N')
set @str=right(@str,len(@str)-1)
return @str
end
go
select car_no,dbo.getstr(car_no,zone) zone,sum(1) total from 表 group by car_no,zone
as
declare @CarNo int
declare @CarNoTemp int
declare @Zone int
declare @Num int
declare @sql varchar(8000)
delete from zzz_yang_table declare Zone_N cursor for
select car_no,pu_disp_zone,count(pu_disp_zone)as num
from operator_archive where car_no is not null and rtrim(ltrim(car_no))<>'' and status_flag in ( 'D' ,'K' ,'F' ,'N')
group by car_no,pu_disp_zone
order by car_no
OPEN Zone_N
FETCH Zone_N into @CarNo,@Zone,@Num
select @CarNoTemp=0
WHILE @@FETCH_STATUS = 0
BEGIN
if @CarNoTemp=@CarNo
update zzz_yang_table set zone=zone+', ' + convert(varchar(5),@Zone) +'-' +convert(varchar(5),@Num) + ' of calls',Total=total+@Num where carno=@CarNo
else
insert into zzz_yang_table(CarNo,Zone,Total) values ( @CarNo, convert(varchar(5),@Zone) +'-' + convert(varchar(5),@Num) + ' of calls' ,@Num)
select @CarNoTemp=@CarNo
FETCH NEXT from Zone_N into @CarNo,@Zone,@Num
END
CLOSE Zone_N
DEALLOCATE Zone_N
select * from zzz_yang_table
GO