Reproting Servers报表SQL查询语句

Question

SQL代码：select ( case when plst_odds=20 then sum(e.plsc_trackcount*e.plsc_cost) end )as Low,
    (Case when plst_odds=50 then sum(e.plsc_trackcount*e.plsc_cost) end)as Middle,
    (case when plst_odds=90 then sum(e.plsc_trackcount*e.plsc_cost) end)as high,
    convert(int,month(e.plsc_subtime))as subtime from 
    plstrack k,plsapplication p,plscourse e where 
    p.plsa_plstrackid=k.plst_plstrackID
     and  p.plsa_coursename=e.plsc_plscourseID and
      e.plsc_Deleted is null and p.plsa_Deleted is null and
       k.plst_Deleted is null and e.plsc_subtime 
 BETWEEN convert(varchar,2011)+'-'+convert(varchar,1)+'-'+convert(varchar,1) AND 
   convert(varchar,dateadd(month,1,convert(varchar,2011)+'-'+convert(varchar,12)+'-'+convert(varchar,31)))
   group by convert(int,month(e.plsc_subtime)),plst_odds
错误运行出来的结果：LOW    Middle    high           subtime
null   null      null              9
3000   null      null             10
200    null      null             11
null   400       null             10
null   null      null             11
null   nulll      600             10我想要的结果 ：——
Low     Middle     high     subtime
3000     400        600       10
200       null       null     11

Answer 1

select max(( case when plst_odds=20 then sum(e.plsc_trackcount*e.plsc_cost) end ))as Low,
  max((Case when plst_odds=50 then sum(e.plsc_trackcount*e.plsc_cost) end))as Middle,
  max((case when plst_odds=90 then sum(e.plsc_trackcount*e.plsc_cost) end))as high,
  convert(int,month(e.plsc_subtime))as subtime from  
  plstrack k,plsapplication p,plscourse e where  
  p.plsa_plstrackid=k.plst_plstrackID
  and p.plsa_coursename=e.plsc_plscourseID and
  e.plsc_Deleted is null and p.plsa_Deleted is null and
  k.plst_Deleted is null and e.plsc_subtime  
 BETWEEN convert(varchar,2011)+'-'+convert(varchar,1)+'-'+convert(varchar,1) AND  
  convert(varchar,dateadd(month,1,convert(varchar,2011)+'-'+convert(varchar,12)+'-'+convert(varchar,31)))
  group by convert(int,month(e.plsc_subtime)),plst_odds