计算每个人平均处理子单据的时间解决思路

计算每个人平均处理子单据的时间
主表
BILLID CREATTIME UPDATETIME CREATER
1001 2011-09-11 09:00 2011-09-11 09:06 AAA
1002 2011-09-11 09:00 2011-09-11 10:06 BBB
1003 2011-09-11 09:00 2011-09-11 09:30 AAA
明细表
BILLID PID  
1001 P1001  
1001 P1002
1002 P1001
1002 P1002
1003 P1001

要求计算每个人平均处理子单据的时间:
例如A主单据花费时间总和为36分钟,子单据数量为3,平均时间为12
  B主单据花费时间总和为66分钟,子单据数量为2,平均时间为33
CREATER 花费
 AAA 12分钟
 BBB 33分钟

[解决办法]

SQL code

------------------------------ Author  :fredrickhu(小F，向高手学习)-- Date    :2011-11-07 17:24:45-- Version:--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) --    Apr 22 2011 11:57:00 --    Copyright (c) Microsoft Corporation--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[主表]if object_id('[主表]') is not null drop table [主表]go create table [主表]([BILLID] int,[CREATTIME] datetime,[UPDATETIME] datetime,[CREATER] varchar(3))insert [主表]select 1001,'2011-09-11 09:00','2011-09-11 09:06','AAA' union allselect 1002,'2011-09-11 09:00','2011-09-11 10:06','BBB' union allselect 1003,'2011-09-11 09:00','2011-09-11 09:30','AAA'--> 测试数据：[明细表]if object_id('[明细表]') is not null drop table [明细表]go create table [明细表]([BILLID] int,[PID] varchar(5))insert [明细表]select 1001,'P1001' union allselect 1001,'P1002' union allselect 1002,'P1001' union allselect 1002,'P1002' union allselect 1003,'P1001'--------------开始查询--------------------------select   a.CREATER,ltrim(a.num/b.num)  +'分钟' as  花费,排名=DENSE_RANK()OVER(ORDER BY a.num/b.num desc)from(select BILLID,CREATER,SUM(datediff(mi,CREATTIME,UPDATETIME)) as num from 主表 group by CREATER,BILLID)a,(select PID,COUNT(1) as num from 明细表 group by PID)bwhere  a.BILLID=RIGHT(pid,4)----------------结果----------------------------/* CREATER 花费               排名------- ---------------- --------------------BBB     33分钟             1AAA     2分钟              2(2 行受影响)*/
[解决办法]
SQL code
if object_id('[主表]') is not null drop table [主表]go create table [主表]([BILLID] int,[CREATTIME] datetime,[UPDATETIME] datetime,[CREATER] varchar(3))insert [主表]select 1001,'2011-09-11 09:00','2011-09-11 09:06','AAA' union allselect 1002,'2011-09-11 09:00','2011-09-11 10:06','BBB' union allselect 1003,'2011-09-11 09:00','2011-09-11 09:30','AAA'--> 测试数据：[明细表]if object_id('[明细表]') is not null drop table [明细表]go create table [明细表]([BILLID] int,[PID] varchar(5))insert [明细表]select 1001,'P1001' union allselect 1001,'P1002' union allselect 1002,'P1001' union allselect 1002,'P1002' union allselect 1003,'P1001'select [CREATER],SUM(distinct DATEDIFF(mi,[CREATTIME],[UPDATETIME]))/COUNT(a.[BILLID])from [主表] a,[明细表] b where a.[BILLID]=b.[BILLID] group by [CREATER]/*CREATER ------- -----------AAA     12BBB     33(2 行受影响)
[解决办法]
SQL code
create table tf(BILLID int,CREATTIME datetime,UPDATETIME datetime,CREATER varchar(10))insert into tf select 1001,'2011-09-11 09:00','2011-09-11 09:06','AAA'insert into tf select 1002,'2011-09-11 09:00','2011-09-11 10:06','BBB'insert into tf select 1003,'2011-09-11 09:00','2011-09-11 09:30','AAA'create table ts(BILLID int,PID varchar(10))insert into ts select 1001,'P1001'   insert into ts select 1001,'P1002'insert into ts select 1002,'P1001'insert into ts select 1002,'P1002'insert into ts select 1003,'P1001'goselect a.creater,SUM(datediff(mi,a.creattime,a.updatetime))/SUM(b.ct)花费from tf a inner join (select billid,COUNT(*) ct from ts group by billid)bon a.BILLID=b.BILLID group by a.CREATER /*creater    花费---------- -----------AAA        12BBB        33(2 行受影响)*/godrop table ts,tf