这样的要求能用一条SQL语句写出来吗？
员工表 TP
员工代码 姓名 薪水 职务
psnCode, psnName, psnSalary, psnDuty
101 张三 2000.00 总经理
102 李四 3000.00 副总经理
103 王五 4000.00 业务员
104 小六 5000.00 总监
105 小7 5500.00 总监
销售主表TM
销售主表ID 员工代码 销售日期
SaleID, psnCode, Date
A001 101 2013-12-09 12:00:13
A002 101 2013-10-09 12:00:13
A003 102 2013-11-09 12:00:15
......
销售子表TD
子表ID 主表ID 物品ID 物品数量 物品总价
TDID, SaleID, wuID, nNums, nPrice
1 A001 A 100 2500
2 A001 B 150 2530
3 A001 C 50 12530
4 A002 A 150 112530
要求：
现在要查找在指定时间范围内的，指定职务的所有人员每个月的销售数量和物品总价格(不区分具体物品)
比如：查询总监职务的人员在2013-10,2013-12这3个月中，每个月的销售数量和总价值
104 小六 2013-10 1890 23456.32
104 小六 2013-11 2890 22456.32
104 小六 2013-12 3890 28456.32
105 小7 2013-10 1890 23456.32
106 小7 2013-11 2890 22456.32
107 小7 2013-12 3890 28456.32
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-20 14:19:27
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([psnCode] int,[psnName] varchar(4),[psnSalary] numeric(6,2),[psnDuty] varchar(8))
insert [TB]
select 101,'张三',2000.00,'总经理' union all
select 102,'李四',3000.00,'副总经理' union all
select 103,'王五',4000.00,'业务员' union all
select 104,'小六',5000.00,'总监' union all
select 105,'小7',5500.00,'总监'
--> 测试数据：[TM]
if object_id('[TM]') is not null drop table [TM]
go
create table [TM]([SaleID] varchar(4),[psnCode] int,[Date] datetime)
insert [TM]
select 'A001',101,'2013-12-09 12:00:13' union all
select 'A002',101,'2013-10-09 12:00:13' union all
select 'A003',102,'2013-11-09 12:00:15'
--> 测试数据：[TD]
if object_id('[TD]') is not null drop table [TD]
go
create table [TD]([TDID] int,[SaleID] varchar(4),[wuID] varchar(1),[nNums] int,[nPrice] int)
insert [TD]
select 1,'A001','A',100,2500 union all
select 2,'A001','B',150,2530 union all
select 3,'A001','C',50,12530 union all
select 4,'A002','A',150,112530
--------------开始查询--------------------------
select b.*,SUM(d.nNums)nNums,SUM(d.nPrice)nPrice,LEFT(CONVERT(VARCHAR(10),[Date],112),4)+'-'+SUBSTRING(CONVERT(VARCHAR(10),[Date],112),PATINDEX('%-%',CONVERT(VARCHAR(10),[Date],112))+1,2)[date]
from [TB] b LEFT JOIN TM m ON b.psncode=m.psncode
LEFT JOIN TD d ON m.saleid=d.saleid
WHERE LEFT(CONVERT(VARCHAR(10),[Date],112),4)+'-'+SUBSTRING(CONVERT(VARCHAR(10),[Date],112),PATINDEX('%-%',CONVERT(VARCHAR(10),[Date],112))+1,2) BETWEEN 查询起始日期 AND 查询结束日期
AND b.psnduty='特定人员'
GROUP BY b.psncode,b.psnname,b.psnsalary,b.psnduty,LEFT(CONVERT(VARCHAR(10),[Date],112),4)+'-'+SUBSTRING(CONVERT(VARCHAR(10),[Date],112),PATINDEX('%-%',CONVERT(VARCHAR(10),[Date],112))+1,2)
----------------结果----------------------------
/*
*/
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([psnCode] int,[psnName] varchar(4),[psnSalary] numeric(6,2),[psnDuty] varchar(8))
insert [TB]
select 101,'张三',2000.00,'总经理' union all
select 102,'李四',3000.00,'副总经理' union all
select 103,'王五',4000.00,'业务员' union all
select 104,'小六',5000.00,'总监' union all
select 105,'小7',5500.00,'总监'
if object_id('[TM]') is not null drop table [TM]
go
create table [TM]([SaleID] varchar(4),[psnCode] int,[Date] datetime)
insert [TM]
select 'A001',101,'2013-12-09 12:00:13' union all
select 'A002',101,'2013-10-09 12:00:13' union all
select 'A003',102,'2013-11-09 12:00:15'
if object_id('[TD]') is not null drop table [TD]
go
create table [TD]([TDID] int,[SaleID] varchar(4),[wuID] varchar(1),[nNums] int,[nPrice] int)
insert [TD]
select 1,'A001','A',100,2500 union all
select 2,'A001','B',150,2530 union all
select 3,'A001','C',50,12530 union all
select 4,'A002','A',150,112530
go
select tb.psnCode,tb.psnName,CONVERT(varchar(7),tm.date,120) as 年月,
SUM(nNums) as 销售数量,
sum([nPrice]) as 总价值
from TM
inner join TD
on tm.[SaleID] = td.[SaleID]
inner join [TB]
on tb.psnCode = tm.psnCode
group by tb.psnCode,tb.psnName,CONVERT(varchar(7),tm.date,120)
/*
psnCodepsnName年月销售数量总价值
101张三2013-10150112530
101张三2013-1230017560
*/