----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-11-07 14:11:36
-- 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'
--> 测试数据：[明细表]
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'
--------------开始查询--------------------------
;with f as
(
select billid,COUNT(1) as num from 明细表 group by BILLID
)

select
   CREATER,花费=DATEDIFF(mi,a.CREATTIME,a.UPDATETIME)/b.num
from
   主表 a join f b 
on
    a.BILLID=b.BILLID
----------------结果----------------------------
/* CREATER 花费
------- -----------
AAA     3
BBB     33

(2 行受影响)

*/

------解决方案--------------------

SQL code
select CREATER, DATEDIFF(MI ,CREATTIME,UPDATETIME)/B.num As '花费'
,ROW_NUMBER () over(order by getdate()) as '排序'
from 主表 A 
left join 
(select BILLID ,COUNT(PID) as num from 明细表 group by BILLID
) as B
on A.BILLID=B.BILLID
order by '花费'

------解决方案--------------------

SQL code
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([BILLID] int,[CREATTIME] Datetime,[UPDATETIME] Datetime,[CREATER] nvarchar(3))
Insert #T
select 1001,'2011-09-11 09:00','2011-09-11 09:06',N'AAA' union all
select 1002,'2011-09-11 09:00','2011-09-11 10:06',N'BBB'
Go

 
if not object_id(N'Tempdb..#T2') is null
    drop table #T2
Go
Create table #T2([BILLID] int,[PID] nvarchar(5))
Insert #T2
select 1001,N'P1001' union all
select 1001,N'P1002' union all
select 1002,N'P1001' union all
select 1002,N'P1002'
GO

SELECT 
    [CREATER],
    STR(n/COUNT(*), 5,0)+N'分钟' AS 花费
FROM 
    (SELECT *,DATEDIFF(n,[CREATTIME],[UPDATETIME]) AS n FROM #T) AS t
    INNER JOIN #T2 AS t2 ON T.[BILLID]=t2.[BILLID]
GROUP BY [CREATER],n

/*
CREATER    花费
AAA        3分钟
BBB       33分钟
*/

------解决方案--------------------

SQL code
----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-11-07 14:11:36
-- 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 1001,'2011-09-11 09:00','2011-09-11 09:20','AAA'
--> 测试数据：[明细表]
if object_id('[明细表]') is not null drop table [明细表]
go 
create table [明细表]([BILLID] int,[PID] varchar(5))