表名: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
字段: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
(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
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