交叉查询并求和,不要显示0
各位高手,我有一个交叉查询,目标是为了按pathid分组,并求出各并对breed的值求和,功能已实现,但是显示的结果有很多0,我想把为0的数据都变成空,不显示出来,该怎么改进,请指教!谢谢
CREATE procedure he_o;1
as
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',sum(case when pathid= ' ' '+pathid+ ' ' ' then amount else ' ' ' ' end) as [ '+pathid+ '] '
from he_productorder_view group by pathid
exec( 'select sum(amount) as amount,breed '+ @sql+ ' from he_productorder_view group by breed,pid order by pid asc ')
GO
[解决办法]
try
CREATE procedure he_o;1
as
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',max(case when pathid= ' ' '+pathid+ ' ' ' then Rtrim(amount) else ' ' ' ' end) as [ '+pathid+ '] '
from he_productorder_view group by pathid
exec( 'select sum(amount) as amount,breed '+ @sql+ ' from he_productorder_view group by breed,pid order by pid asc ')
GO
[解决办法]
try:
--加上and amount> 0
CREATE procedure he_o;1
as
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',sum(case when pathid= ' ' '+pathid+ ' ' ' and amount> 0 then amount else ' ' ' ' end) as [ '+pathid+ '] '
from he_productorder_view group by pathid
exec( 'select sum(amount) as amount,breed '+ @sql+ ' from he_productorder_view group by breed,pid order by pid asc ')
GO
