数据库表
id area city category productname saleamount saletime
1 华南 广州 饮料 可乐 340 2005-8-9
2 华北 北京 饮料 可乐 890 2005-7-10
3 华南 广州 饮料 可乐 360 2005-9-10
...
怎样从这个表中一次取出同比、环比数据?谢谢了如果需要测试数据及其他资料,在线联系我,我发给你
id area city category productname saleamount saletime
1 华南 广州 饮料 可乐 340 2005-8-9
2 华北 北京 饮料 可乐 890 2005-7-10
3 华南 广州 饮料 可乐 360 2005-9-10
...
怎样从这个表中一次取出同比、环比数据?谢谢了如果需要测试数据及其他资料,在线联系我,我发给你
convert(varchar(7),a.saletime,120) as 月份,
sum(saleamount) as 总量,
1.0*sum(saleamount)/(select sum(saleamount) from tablename where
area=a.area and city=a.city and category=a.category and productname=a.productname
and saletime>=dateadd(month,-12,convert(varchar(7),a.saletime,120)+'-01')
and saletime<dateadd(day,-1,dateadd(month,-11,convert(varchar(7),a.saletime,120)+'-01'))
) as 同比,
1.0*sum(saleamount)/(select sum(saleamount) from tablename where
area=a.area and city=a.city and category=a.category and productname=a.productname
and saletime>=dateadd(month,-1,convert(varchar(7),a.saletime,120)+'-01')
and saletime<dateadd(day,-1,convert(varchar(7),a.saletime,120)+'-01')
) as 环比
from tablename a
group by
area,city,category,productname,
convert(varchar(7),a.saletime,120)
convert(varchar(7),a.saletime,120) as 月份,
sum(saleamount) as 总量,
1.0*sum(saleamount)/(select sum(saleamount) from tablename where
area=a.area and city=a.city
and saletime>=dateadd(month,-12,convert(varchar(7),a.saletime,120)+'-01')
and saletime<dateadd(day,-1,dateadd(month,-11,convert(varchar(7),a.saletime,120)+'-01'))
) as 同比,
1.0*sum(saleamount)/(select sum(saleamount) from tablename where
area=a.area and city=a.city
and saletime>=dateadd(month,-1,convert(varchar(7),a.saletime,120)+'-01')
and saletime<dateadd(day,-1,convert(varchar(7),a.saletime,120)+'-01')
) as 环比
from tablename a
group by
area,city,
convert(varchar(7),a.saletime,120)