select x.*,case when x.sumhit>y.sumhit then '上升' else '下降' end 相对昨日
from (
    select a.articleid,posteddate,mh maxhit,sh sumhit
        from tb a
    inner join
        (
        select id,max(hit) mh,sum(hit) sh from tb group by articleid
        ) b
        on a.id=b.id
    ) x
left join
    (
    select a.articleid,posteddate,mh maxhit,sh sumhit
        from tb a
    inner join
        (
        select id,max(hit) mh,sum(hit) sh from tb group by articleid
        ) b
        on a.id=b.id
    ) y
    on datediff(dd,y.posteddate,x.posteddate)=1

------解决方案--------------------
SQL code
 
 create table table1(id int,articleid int,hit int, caption varchar(10),posteddate datetime) 
 insert into table1 select  1  , 10  ,     1  ,  'a'   ,  '2007-10-22'  
 insert into table1 select   2 ,  10 ,    10  ,    'a'   ,    '2007-10-23'  
 insert into table1 select   3 ,  9  ,     5   ,   'b'  ,   '2007-10-22'  
 insert into table1 select   4 ,  9  ,     4  ,  'b'  ,   '2007-10-23'  
 insert into table1 select   5 ,  8  ,     4  ,  'c'  ,   '2007-10-23'  
 insert into table1 select   6 ,  7  ,     4  ,  'd'   ,   '2007-10-22'  
  
  
 select a.articleid,a.hit [hit(今日点击)],(select max(hit) from table1 where caption=a.caption) [maxhit(最大点击)], 
 convert(varchar(10),a.posteddate,120) [maxHitDate(最大点击日期)],(select sum(hit) from table1 where caption=a.caption)[SumHit(该id点击总数)], 
 case when a.hit-isnull((select hit from table1  where datediff(dd,posteddate,a.posteddate)=1 and caption=a.caption),0)>0 then '上升'  
 when a.hit-isnull((select hit from table1  where datediff(dd,posteddate,a.posteddate)=1 and caption=a.caption),0) <0 then '下降' else '平' end  [相对昨日]  
 from table1 a,(select max(posteddate) posteddate,caption from table1 group by caption) b 
 where a.posteddate=b.posteddate and a.caption =b.caption order by articleid desc 
  
  
 


------解决方案--------------------
--假设字段posteddate为datetime型且时间部分为0,假设hit=10即10次点击
declare @today datetime --今日日期变量
set @today=convert(datetime,convert(char(8),getdate(),112),112) --获得今天日期时间部分为0
select articleid,
    sum(case when posteddate=@today then hit else 0 end) todayhit,--今日点击
    max(hit) maxHit,
  
    sum(hit) SumHit,
   case when  sum(case when posteddate=@today then hit else 0 end)-
    sum(case when posteddate=dateadd(day,-1,@today) then hit else 0 end)>0 then