计算每个人平均处理时间,该怎么解决
计算每个人平均处理时间
主表
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
明细表
BILLID PID
1001 P1001
1001 P1002
1002 P1001
1002 P1002
要求计算每个人平均处理子单据的时间:
CREATER 花费
AAA 3分钟
BBB 33分钟
------解决方案--------------------
主表
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
明细表
BILLID PID
1001 P1001
1001 P1002
1002 P1001
1002 P1002
要求计算每个人平均处理子单据的时间:
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' --> 测试数据:[明细表] 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)) insert [明细表] select 1001,'P1001' union all select 1001,'P1002' union all select 1002,'P1001' union all select 1002,'P1002' --------------开始查询-------------------------- 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 join (select BILLID,COUNT(1) as num from 明细表 group by BILLID)b on a.BILLID=b.BILLID ----------------结果---------------------------- /CREATER 花费 排名 ------- ---------------- -------------------- BBB 33分钟 1 AAA 13分钟 2 (2 行受影响) */