有三个表,分别如下:
T_BM(部门表) | |
bm_id(部门ID) | bm_name(部门名称) |
A001 | 衣物 |
A002 | 数码产品 |
T_YG(员工表) | |||
yg_id(员工ID) | bm_id(部门ID) | yg_name(员工姓名) | yg_phone(员工电话) |
B001 | A001 | B1 | 12345678 |
B002 | A001 | B2 | 12345679 |
B003 | A002 | B3 | 12345680 |
B004 | A002 | B4 | 12345681 |
T_XL(日销售量表) | |||
bm_id(部门ID) | yg_id(员工ID) | sell(销量) | all_day(统计日期) |
A001 | B001 | 2 | 2013-1-1 |
A001 | B002 | 5 | 2013-1-1 |
A002 | B003 | 4 | 2013-1-1 |
A002 | B004 | 1 | 2013-1-1 |
A001 | B002 | 2 | 2013-1-2 |
A002 | B003 | 2 | 2013-1-2 |
A001 | B001 | 7 | 2013-1-4 |
A001 | B001 | 4 | 2013-1-5 |
A001 | B002 | 4 | 2013-1-5 |
A002 | B003 | 5 | 2013-1-5 |
A002 | B004 | 6 | 2013-1-5 |
A001 | B001 | 2 | 2013-2-1 |
A001 | B002 | 5 | 2013-2-1 |
A002 | B003 | 4 | 2013-2-1 |
A002 | B004 | 1 | 2013-2-1 |
做一个统计每个员工每月的销量的统计。效果如下:
部门 | 月份 | 月销量 | 员工姓名 | 员工电话 |
数码产品 | 201301 | 11 | B3 | 12345680 |
数码产品 | 201301 | 7 | B4 | 12345681 |
数码产品 | 201302 | 4 | B3 | 12345680 |
数码产品 | 201302 | 1 | B4 | 12345681 |
方法是,将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 c
where 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)