----------------------------
-- 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 all
select 1002,'2011-09-11 09:00','2011-09-11 10:06','BBB' union all
select 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 all
select 1001,'P1002' union all
select 1002,'P1001' union all
select 1002,'P1002' union all
select 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)b
where
  a.BILLID=RIGHT(pid,4)


----------------结果----------------------------
/* CREATER 花费               排名
------- ---------------- --------------------
BBB     33分钟             1
AAA     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 all
select 1002,'2011-09-11 09:00','2011-09-11 10:06','BBB' union all
select 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 all
select 1001,'P1002' union all
select 1002,'P1001' union all
select 1002,'P1002' union all
select 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     12
BBB     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'
go
select 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)b
on a.BILLID=b.BILLID 
group by a.CREATER 
/*
creater    花费
-----