这段sql如何写

这段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 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 '上升' 
 
    else '下降' end 相对昨天
   
from table  
group by articleid

最天点击日期一个查询出了来,必须用结果与原表连接再查出.
[解决办法]
select distinct articleId,  
(select hit
 from #tableName
 where posteddate = GetDate() and articleID = A.ArticleID) as TodayHit,

(select Max(hit)
 from #tableName
 where ArticleID = A.ArticleID) as maxHit,

(select posteddate
 from #tableName B
 where hit = (select Max(Hit)
from #tableName
where articleID = B.ArticleID) and ArticleID = A.ArticleID) as MaxHitDate,

(select Sum(Hit)
 from #tableName
 where ArticleID = A.ArticleID) as SumHit,

(select case when ((select Hit
from #tableName
where PostedDate = GetDate()) >  
(select Hit  
from #tableName  
where PostedDate = GetDate() -1)) then '上升'
else '下降'
end ) as Da  
from #tableName A