begin
select
Pb_cgman,sum(pd_quant1) as shul,sum(pd_amount1) as jine, yanChi
from ViewPurchase
where pb_date >= convert(varchar(10),@mp_stime,120) and pb_date <= convert(varchar(10),@mp_etime,120)
group by Pb_cgman
order by sum(Pd_amount1) desc
end其中的yanChi需要调用一个自定义函数,参数为前面的Pb_cgman,返回值为一个int
select
Pb_cgman,sum(pd_quant1) as shul,sum(pd_amount1) as jine, yanChi
from ViewPurchase
where pb_date >= convert(varchar(10),@mp_stime,120) and pb_date <= convert(varchar(10),@mp_etime,120)
group by Pb_cgman
order by sum(Pd_amount1) desc
end其中的yanChi需要调用一个自定义函数,参数为前面的Pb_cgman,返回值为一个int
/*求两个字符串中相同的汉字或是字母的个数*/
create FUNCTION [dbo].[chinesecount]
(
@StrA nvarchar(200)
)
RETURNS int AS
BEGIN
declare @strBase nvarchar(200)
declare @Rank int
set @rank=0
SELECT @rank=count(*) from dbo.funsplitchar(@StrA) where ascii(item)>127
return @Rank
END/*调用
select dbo.[chinesecount] ('2sdif自3s汉字dsfs')
*/类似这样,你自己改改就行了。
select
Pb_cgman,sum(pd_quant1) as shul,sum(pd_amount1) as jine, yanChi = dbo.函数名称(Pb_cgman)
from ViewPurchase
where pb_date >= convert(varchar(10),@mp_stime,120) and pb_date <= convert(varchar(10),@mp_etime,120)
group by Pb_cgman
order by sum(Pd_amount1) desc
end