这段sql如何写_Sql Server

这段sql怎么写?
table:
id articleId hit(点击) caption posteddate(点击日期)
1 10 1 a 2007-10-22
2 10 10 a 2007-10-23
3 9 5 b 2007-10-22
4 9 4 b 2007-10-23
5 8 4 c 2007-10-23
6 7 4 d 2007-10-22

结果:
articleid TodayHit(今日点击) maxHit(最大点击) maxHitDate(最大点击日期) SumHit(该id点击总数) 相对昨日(上升或下降)

10 10 10 2007-10-23 11 上升
9 4 5 2007-10-23 9 下降
8 4 4 2007-10-23 4 上升

------解决方案--------------------
随手敲的,不排除手误

SQL code

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    ) xleft 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 [email protected] then hit else 0 end) todayhit,--今日点击
    max(hit) maxHit,
  
    sum(hit) SumHit,
   case when  sum(case when [email protected] then hit else 0 end)-
    sum(case when posteddate=dateadd(day,-1,@today) then hit else 0 end)>0 then '上升'