create View Vw_IntegralSearch  
as  
select SystemID, AppSystemName,SUM(UseIntegral) as UseIntegral,loginid,username,UserId  
from(  
select asi.SystemID, asi.AppSystemName,uii.Integral as UseIntegral,uii.LoginId,uii.UserName,uii.UserId  
from UserIntegralInfo uii  
left join AppSystemInfo asi on asi.SystemID = uii.SystemID  
left join IntegralTypes it on it.TypeID = uii.TypeID  
where (isnull(WorkStartTime,'1900-01-01')<=GETDATE() 
and isnull(WorkEndTime,GETDATE()) >= GETDATE()) 
and uii.UseState =2  
union all   
SELECT asi.SystemID, asi.AppSystemName,-icf.SpendIntegral as  UseIntegral,icf.LoginId,icf.UserName,icf.UserId  FROM IntegralConverInfo icf  
left join AppSystemInfo asi on asi.SystemID = icf.SystemID  
) as tb   
group by tb.SystemID ,tb.AppSystemName,tb.loginid,tb.username,tb.UserId