求一个sql！－－－多谢了！
表名:t1
字段：name表示用户名，ordertotal表示订单总额，orderdate表示订单日期,task目标订单额
sql1:查出2013年以前每个销售的销售额
select name,sum(ordertotal) as 'oldorder',task from t1 where orderdate<'2013' group by name,task
例如：
name orderorder task
张三 100 500
李四 110 500
王五 120 500
sql2:查出2013年以后每个销售的销售额
select name,sum(ordertotal) as 'neworder',task from t1 where orderdate>'2013' group by name,task
例如：
name neworder task
张三 200 500
李四 210 500
王五 220 500
现在希望将两个查询拼在一起并计算得到下表：
得出如下效果：
name orderorder neworder heji task wanchenglv
张三 100 200 300 500 0.6
李四 110 210 320 500 0.64
王五 120 220 340 500 0.78
字段heji：表示orderorder+neworder
字段wanchenglv：表示heji/task
[解决办法]
try
select name,orderorder,neworder,heji=orderorder+neworder,task,wanchenglv=cast((orderorder+neworder)*1.0/task as decimal(28,2)) from
(select name,sum(case when orderdate<'2013' then ordertotal else 0 end)[orderorder],sum(case when orderdate>'2013' then ordertotal else 0 end)[neworder],task from t1 group by name,task)
[解决办法]
键盘有点秀逗了，加号答不出来，所以用中文代替
SELECT NAME,SUM([oldorder]) [oldorder],SUM([neworder])[neworder],SUM([oldorder])[加] sum([neworder]) AS [heji],task,
(SUM([oldorder])[加] sum([neworder]))/task AS wanchenglv
FROM (
SELECT name ,
0 AS [oldorder]
SUM(ordertotal) AS [neworder] ,
task
FROM t1
WHERE orderdate > '2013'
GROUP BY name ,
task
UNION
SELECT name ,
SUM(ordertotal) AS [oldorder] ,
0 AS [neworder]
task
FROM t1
WHERE orderdate < '2013'
GROUP BY name ,
task)a
GROUP BY NAME,task
select a.name,a.oldorder,a.neworder,a.oldorder+a.neworder as heji,
a.task,a.heji/a.task as wanchenglv from
(select name,task,
sum(case when year(orderdate)<2013 then ordertotal else 0 end) as oldorder ,
sum(case when year(orderdate)>=2013 then ordertotal else 0 end) as neworder
from t1 group by name,task) a
