有一个(任务分配表a),表结构如下:
ID 角色ID 年度 月份 任务额(单位元) ...
1 01 2007 01 12000.00 ...
2 01 2007 02 13000.00 ...
3 01 2007 03 14000.00 ...
4 02 2007 01 16000.00 ...
5 03 2007 01 17500.00 ...
...
表示每个月都会为不同的角色分配任务额
另有一个(销售订单表b),表结构如下:
ID 角色ID 订单日期 订单额(单位元) ...
1 01 2007-01-01 600.00 ...
2 01 2007-01-01 700.00 ...
3 01 2007-01-04 500.00 ...
4 03 2007-01-02 300.00 ...
5 02 2007-02-05 800.00 ...
...
表示对每笔订单进行记录
需要实现的功能:分统计角色当前年1月份至当前月的任务总额、订单总额以及当前月的任务额、订单总额请各位大侠帮忙,在线等。
ID 角色ID 年度 月份 任务额(单位元) ...
1 01 2007 01 12000.00 ...
2 01 2007 02 13000.00 ...
3 01 2007 03 14000.00 ...
4 02 2007 01 16000.00 ...
5 03 2007 01 17500.00 ...
...
表示每个月都会为不同的角色分配任务额
另有一个(销售订单表b),表结构如下:
ID 角色ID 订单日期 订单额(单位元) ...
1 01 2007-01-01 600.00 ...
2 01 2007-01-01 700.00 ...
3 01 2007-01-04 500.00 ...
4 03 2007-01-02 300.00 ...
5 02 2007-02-05 800.00 ...
...
表示对每笔订单进行记录
需要实现的功能:分统计角色当前年1月份至当前月的任务总额、订单总额以及当前月的任务额、订单总额请各位大侠帮忙,在线等。
--@T1 ASSIGNMENT TABLE
DECLARE @T1 TABLE(ID INT,ROLEID VARCHAR(10),YEARS VARCHAR(10),MONTHS VARCHAR(10),TASKBALANCE DEC(8,2))
INSERT @T1 SELECT 1,'01','2007','01',12000.00
UNION ALL SELECT 2,'01','2007','02',13000.00
UNION ALL SELECT 3,'01','2007','03',14000.00
UNION ALL SELECT 4,'02','2007','01',16000.00
UNION ALL SELECT 5,'03','2007','01',17500.00
--@T2 SALES TABLE
DECLARE @T2 TABLE(ID INT,ROLEID VARCHAR(10),ORDERDATE DATETIME,ORDERBALANCE DEC(8,2) )
INSERT @T2 SELECT 1,'01','2007-01-01',600.00
UNION ALL SELECT 2,'01','2007-01-01',700.00
UNION ALL SELECT 3,'01','2007-01-04',500.00
UNION ALL SELECT 4,'02','2007-01-02',300.00
UNION ALL SELECT 5,'03','2007-02-05',800.00
--SQL implementation
--统计角色当前年1月份至当前月的任务总额、订单总额
SELECT
A.ROLEID,
SUM(A.TASKBALANCE) AS TASKBALANCE,
ORDERBALANCE=ISNULL((SELECT SUM(B.ORDERBALANCE) FROM @T2 B WHERE ROLEID=A.ROLEID AND YEAR(ORDERDATE)=YEAR(GETDATE())),0)
FROM @T1 A
WHERE A.YEARS=YEAR(GETDATE())
GROUP BY A.ROLEID
--当前月的任务额、订单总额
SELECT
A.ROLEID,
SUM(A.TASKBALANCE) AS TASKBALANCE,
ORDERBALANCE=ISNULL((SELECT SUM(B.ORDERBALANCE) FROM @T2 B WHERE ROLEID=A.ROLEID AND MONTH(ORDERDATE)=MONTH(GETDATE())),0)
FROM @T1 A
WHERE A.MONTHS=RIGHT(MONTH(GETDATE())+100,2)
GROUP BY A.ROLEID
A.ROLEID,
SUM(A.TASKBALANCE) AS TOTALTASKBALANCE,
TOTALORDERBALANCE=ISNULL((SELECT SUM(B.ORDERBALANCE) FROM @T2 B WHERE ROLEID=A.ROLEID AND YEAR(ORDERDATE)=YEAR(GETDATE())),0),
CURMONTHTASKBALANCE=ISNULL((SELECT SUM(A.TASKBALANCE) FROM @T1 B WHERE ROLEID=A.ROLEID AND MONTHS=RIGHT(MONTH(GETDATE())+100,2)),0),
CURMONTHORDERBALANCE=ISNULL((SELECT SUM(B.ORDERBALANCE) FROM @T2 B WHERE ROLEID=A.ROLEID AND MONTH(ORDERDATE)=MONTH(GETDATE())),0)
FROM @T1 A
WHERE A.YEARS=YEAR(GETDATE())
GROUP BY A.ROLEID
declare @A table(ID int,角色ID varchar(2),年度 int,月份 tinyint,任务额 money)
insert @A
select 1,'01',2007,1,12000.00 union all
select 2,'01',2007,2,13000.00 union all
select 3,'01',2007,3,14000.00 union all
select 4,'02',2007,1,16000.00 union all
select 5,'03',2007,1,17500.00
--原始数据:@B
declare @B table(ID int,角色ID varchar(2),订单日期 datetime,订单额 money)
insert @B
select 1,'01','2007-01-01',600.00 union all
select 2,'01','2007-01-01',700.00 union all
select 3,'01','2007-01-04',500.00 union all
select 4,'03','2007-01-02',300.00 union all
select 5,'02','2007-02-05',800.0declare @Y int, @M tinyint
select @Y=year(getdate()),@M=month(getdate())select
角色ID=isnull(a.角色ID,c.角色ID),
年度=@Y,
任务总额=isnull(a.任务总额,0),
订单总额=isnull(c.订单总额,0),
本月=@M,
本月总额=isnull(b.本月总额,0),
本月订单=isnull(d.本月订单,0)
from
(select 角色ID,任务总额=sum(任务额) from @A where 年度=@Y and 月份 between 1 and @M group by 角色ID) a
left join
(select 角色ID,本月总额=sum(任务额) from @A where 年度=@Y and 月份=@M group by 角色ID) b
on a.角色ID=b.角色ID
full join
(select 角色ID,订单总额=sum(订单额) from @B where year(订单日期)=@Y and month(订单日期) between 1 and @M group by 角色ID) c
on a.角色ID=c.角色ID
left join
(select 角色ID,本月订单=sum(订单额) from @B where year(订单日期)=@Y and month(订单日期)=@M group by 角色ID) d
on a.角色ID=d.角色ID/*
角色ID 年度 任务总额 订单总额 本月 本月总额 本月订单
---- ----------- --------------------- --------------------- ---- --------------------- ---------------------
01 2007 39000.0000 1800.0000 10 .0000 .0000
02 2007 16000.0000 800.0000 10 .0000 .0000
03 2007 17500.0000 300.0000 10 .0000 .0000
*/