按月统计的sql语句

来源：互联网发布：中国青年网络微博编辑：程序博客网时间：2024/05/16 10:48

有三个表，分别如下：

T_BM(部门表)bm_id(部门ID)bm_name(部门名称)A001衣物A002数码产品
T_YG(员工表) yg_id(员工ID)bm_id(部门ID)yg_name(员工姓名)yg_phone(员工电话)B001A001B112345678B002A001B212345679B003A002B312345680B004A002B412345681

T_XL(日销售量表) bm_id(部门ID)yg_id(员工ID)sell(销量)all_day(统计日期)A001B00122013-1-1A001B00252013-1-1A002B00342013-1-1A002B00412013-1-1A001B00222013-1-2A002B00322013-1-2A001B00172013-1-4A001B00142013-1-5A001B00242013-1-5A002B00352013-1-5A002B00462013-1-5A001B00122013-2-1A001B00252013-2-1A002B00342013-2-1A002B00412013-2-1

做一个统计每个员工每月的销量的统计。效果如下：

部门月份月销量员工姓名员工电话数码产品20130111B312345680数码产品2013017B412345681数码产品2013024B312345680数码产品2013021B412345681

方法是，将all_day的格式转化一下，加入group by 即可。

select c.bm_name 部门名称,convert(char(6),all_day,112) 年月,sum(a.sell) 月销量,b.yg_name 员工名,b.yg_phone 员工电话from T_XL a,T_YG b,T_BM cwhere a.bm_id='A002' and a.yg_id=b.yg_id --and all_day between '2013-1-1' and '2013-2-1'group by c.bm_name,b.yg_name,b.yg_phone,convert(char(6),all_day,112)