同比、环比sql

indure 2007-07-04 10:07:57

数据库表
id area city category productname saleamount saletime
1 华南广州饮料可乐 340 2005-8-9
2 华北北京饮料可乐 890 2005-7-10
3 华南广州饮料可乐 360 2005-9-10
...
怎样从这个表中一次取出同比、环比数据？

谢谢了

如果需要测试数据及其他资料，在线联系我，我发给你

...全文

610 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

昵称被占用了 2007-07-04

打赏
举报

只到片区和城市

select area,city,
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)

昵称被占用了 2007-07-04

打赏
举报

晕哦，搞这么复杂的报表

昵称被占用了 2007-07-04

打赏
举报

select area,city,category,productname,
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)

indure 2007-07-04