sql 2005 三表查询分组统计 好难啊

Question

if object_id('tb') is not null 
drop table tb 
create table tb(type varchar(20),accessoryNo varchar(20),deviceNo varchar(10),lastTime int) 
insert tb 
select 'A','s10','z11',10 union all 
select 'B','s10','z11',11 union all 
select 'C','s11','z12',5 union all 
select 'A','s10','z11',9 union all 
select 'A','s10','z13',9 union all 
select 'B','s10','z11',3 
if object_id('fees') is not null 
drop table fees
create table fees(id int,fee numeric(18, 3))
insert fees 
select 1, 50.00
if object_id('devices') is not null 
drop table devices
create table devices(id int,deviceNo varchar(10),powers numeric(18,3))
insert devices 
select 1, 'z11',3.00 union all
select 2, 'z12',50.00 union all
select 3, 'z13',25.00go 
--如果是SQLSERVER2000，那么需要改用临时表
;WITH CTE1 AS (
SELECT DISTINCT a.[type],a.accessoryNo,b.fee,c.TotalLastTime,d.TotalPowers
FROM tb AS a INNER JOIN fees AS b ON b.id=1 
INNER JOIN (SELECT TotalLastTime=SUM(LastTime) FROM tb) AS c ON 1=1 
INNER JOIN (SELECT TotalPowers=SUM(x.LastTime*y.powers) FROM tb AS x INNER JOIN devices AS y ON x.deviceNo=y.deviceNo) AS d ON 1=1 
) 
SELECT 
[序号]=ROW_NUMBER() OVER(ORDER BY [type])
,[型号]=[type]
,[零件号]=accessoryNo
,[加工时间]=(SELECT SUM(lastTime) FROM tb AS x WHERE x.type=a.type AND x.accessoryNo=a.accessoryNo)
,[分配额]=1.0*(SELECT SUM(lastTime) FROM tb AS x WHERE x.type=a.type AND x.accessoryNo=a.accessoryNo)/a.TotalLastTime*a.fee
,[时间功耗]=(SELECT TotalPowers=SUM(x.LastTime*y.powers) FROM tb AS x INNER JOIN devices AS y ON x.deviceNo=y.deviceNo
WHERE x.type=a.type AND x.accessoryNo=a.accessoryNo )
,[精确分配额]=(SELECT TotalPowers=SUM(x.LastTime*y.powers) FROM tb AS x INNER JOIN devices AS y ON x.deviceNo=y.deviceNo
WHERE x.type=a.type AND x.accessoryNo=a.accessoryNo
)/a.TotalPowers *a.fee
FROM CTE1 AS a
UNION ALL
SELECT 
[序号]=0
,[型号]='全部型别'
,[零件号]='全部零件'
,[加工时间]=a.TotalLastTime
,[分配额]=a.fee
,[时间功耗]=a.TotalPowers
,[精确分配额]=a.fee
FROM (SELECT DISTINCT TotalLastTime,TotalPowers,fee FROM CTE1) AS a 
drop table tb 
drop table fees
drop table devices--查询的结果是(3 行受影响)
序号                   型号                   零件号                  加工时间        分配额                                     时间功耗                                    精确分配额
-------------------- -------------------- -------------------- ----------- --------------------------------------- --------------------------------------- ---------------------------------------
1                    A                    s10                  28          29.7872340426                           282.000                                 24.564450
2                    B                    s10                  14          14.8936170213                           42.000                                  3.658500
3                    C                    s11                  5           5.3191489362                            250.000                                 21.777000
0                    全部型别                 全部零件                 47          50.0000000000                           574.000                                 50.000000(4 行受影响)统计结果是这么计算出来的序号 型别 零件号 加工时间 分配额 时间功耗 精确分配额
1 A s10 28 28/47*50.00 （（10+9）*3.0+9*25.0） （（10+9）*3.0+9*25.0）/T*50.0
2 B s10 14 14/47*50.00 （3+11）*3.0 （3+11）*3.0/T*50.0
3 C s11 5 5/47*50.00 5*50.0 5*50.0/T*50.0
0 全部型别 全部零件 47 50.00 （（10+9）*3.0+9*25.0）+（3+11）*3.0+5*50.0 as T 50现在我的要求是按照设备编号（deviceNo）分一下组
即序号 设备编号 型别 零件号 加工时间 分配额    时间功耗     精确分配额
1     z11      A    s10   10+9     19/47*50 （10+9）*3.0  （（10+9）*3.0）/T*50.02     z11      B    s10   11+3     14/47*50  （3+11）*3.0  （（3+11）*3.0）/T*50

0 全部型别 全部零件 47 50.00 （（10+9）*3.0+9*25.0）+（3+11）*3.0+5*50.0 as T 50

Answer 1

if object_id('tb') is not null 
drop table tb 
create table tb(type varchar(20),accessoryNo varchar(20),deviceNo varchar(10),lastTime int) 
insert tb 
select 'A','s10','z11',10 union all 
select 'B','s10','z11',11 union all 
select 'C','s11','z12',5 union all 
select 'A','s10','z11',9 union all 
select 'A','s10','z13',9 union all 
select 'B','s10','z11',3 
if object_id('fees') is not null 
drop table fees
create table fees(id int,fee numeric(18, 3))
insert fees 
select 1, 50.00
if object_id('devices') is not null 
drop table devices
create table devices(id int,deviceNo varchar(10),powers numeric(18,3))
insert devices 
select 1, 'z11',3.00 union all
select 2, 'z12',50.00 union all
select 3, 'z13',25.00go 
--如果是SQLSERVER2000，那么需要改用临时表
;WITH CTE1 AS (
SELECT DISTINCT a.[type],a.deviceNo,a.accessoryNo,b.fee,c.TotalLastTime,d.TotalPowers
FROM tb AS a INNER JOIN fees AS b ON b.id=1 
INNER JOIN (SELECT TotalLastTime=SUM(LastTime) FROM tb) AS c ON 1=1 
INNER JOIN (SELECT TotalPowers=SUM(x.LastTime*y.powers) FROM tb AS x INNER JOIN devices AS y ON x.deviceNo=y.deviceNo) AS d ON 1=1 
) 
SELECT 
[序号]=ROW_NUMBER() OVER(ORDER BY [type])
,[型号]=[type]
,[设备编号]=deviceNo
,[零件号]=accessoryNo
,[加工时间]=(SELECT SUM(lastTime) FROM tb AS x WHERE x.type=a.type AND x.deviceNo=a.deviceNo and x.accessoryNo=a.accessoryNo)
,[分配额]=1.0*(SELECT SUM(lastTime) FROM tb AS x WHERE x.type=a.type AND x.deviceNo=a.deviceNo AND x.accessoryNo=a.accessoryNo)/a.TotalLastTime*a.fee
,[时间功耗]=(SELECT TotalPowers=SUM(x.LastTime*y.powers) FROM tb AS x INNER JOIN devices AS y ON x.deviceNo=y.deviceNo
WHERE x.type=a.type AND x.accessoryNo=a.accessoryNo )
,[精确分配额]=(SELECT TotalPowers=SUM(x.LastTime*y.powers) FROM tb AS x INNER JOIN devices AS y ON x.deviceNo=y.deviceNo
WHERE x.type=a.type AND x.accessoryNo=a.accessoryNo AND x.deviceNo=a.deviceNo 
)/a.TotalPowers *a.fee
FROM CTE1 AS a
UNION ALL
SELECT 
[序号]=0
,[型号]='全部型别'
,[设备编号]='全部设备'
,[零件号]='全部零件'
,[加工时间]=a.TotalLastTime
,[分配额]=a.fee
,[时间功耗]=a.TotalPowers
,[精确分配额]=a.fee
FROM (SELECT DISTINCT TotalLastTime,TotalPowers,fee FROM CTE1) AS a 
/*
序号 型号 设备编号 零件号 加工时间 分配额 时间功耗 精确分配额
1 A z11 s10 19 20.2127659574 282.000 4.965150
2 A z13 s10 9 9.5744680851 282.000 19.599300
3 B z11 s10 14 14.8936170213 42.000 3.658500
4 C z12 s11 5 5.3191489362 250.000 21.777000
0 全部型别 全部设备 全部零件 47 50.0000000000 574.000 50.000000
*/drop table tb 
drop table fees
drop table devices

Answer 2

if object_id('tb') is not null 
drop table tb 
create table tb(type varchar(20),accessoryNo varchar(20),deviceNo varchar(10),lastTime int) 
insert tb 
select 'A','s10','z11',10 union all 
select 'B','s10','z11',11 union all 
select 'C','s11','z12',5 union all 
select 'A','s10','z11',9 union all 
select 'A','s10','z13',9 union all 
select 'B','s10','z11',3 
if object_id('fees') is not null 
drop table fees
create table fees(id int,fee numeric(18, 3))
insert fees 
select 1, 50.00
if object_id('devices') is not null 
drop table devices
create table devices(id int,deviceNo varchar(10),powers numeric(18,3))
insert devices 
select 1, 'z11',3.00 union all
select 2, 'z12',50.00 union all
select 3, 'z13',25.00go 
/*
序号 设备编号 型别 零件号 加工时间 分配额 时间功耗 精确分配额
1 z11 A s10 10+9 19/47*50 （10+9）*3.0 （（10+9）*3.0）/T*50.02 z11 B s10 11+3 14/47*50 （3+11）*3.0 （（3+11）*3.0）/T*50

0 全部型别 全部零件 47 50.00 （（10+9）*3.0+9*25.0）+（3+11）*3.0+5*50.0 as T 50
*/select 
t.deviceno as 设备编号,
t.type as 型别,
t.accessoryno as 零件号,
sum(t.lasttime) as 加工时间,
sum(t.lasttime)*1.0/(select sum(lasttime) from tb)*r.fee as 分配额,
sum(t.lasttime)*h.powers as 时间功耗,
sum(t.lasttime)*h.powers*r.fee/(select sum(f.lasttime*g.powers) from tb f join devices g
on f.deviceno = g.deviceno ) as 精确分配额
from tb t join fees r on 1 = 1
join devices h
on t.deviceno = h.deviceno
group by t.deviceno,t.type,t.accessoryno,r.fee,h.powers
union all
select '全部设备','全部型别','全部零件',
(select sum(lasttime) from tb),
(select fee from fees),
sum(f.lasttime*g.powers),
(select fee from fees)
from tb f join devices g
on f.deviceno = g.deviceno
设备编号       型别                   零件号                  加工时间        分配额                                      时间功耗                                     精确分配额                                    
---------- -------------------- -------------------- ----------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 
z11        A                    s10                  19          20.2127659574                            57.000                                   4.965156
z11        B                    s10                  14          14.8936170213                            42.000                                   3.658536
z12        C                    s11                  5           5.3191489362                             250.000                                  21.777003
z13        A                    s10                  9           9.5744680851                             225.000                                  19.599303
全部设备       全部型别                 全部零件                 47          50.0000000000                            574.000                                  50.000000（所影响的行数为 5 行）