计算每个人平均处理时间
主表
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分钟
[解决办法]
------------------------------ 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 allselect 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 allselect 1001,'P1002' union allselect 1002,'P1001' union allselect 1002,'P1002'--------------开始查询--------------------------;with f as(select billid,COUNT(1) as num from 明细表 group by BILLID)select CREATER,花费=DATEDIFF(mi,a.CREATTIME,a.UPDATETIME)/b.numfrom 主表 a join f b on a.BILLID=b.BILLID----------------结果----------------------------/* CREATER 花费------- -----------AAA 3BBB 33(2 行受影响)*/
[解决办法]
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 Bon A.BILLID=B.BILLIDorder by '花费'
[解决办法]
use Tempdbgo--> --> if not object_id(N'Tempdb..#T') is null drop table #TGoCreate table #T([BILLID] int,[CREATTIME] Datetime,[UPDATETIME] Datetime,[CREATER] nvarchar(3))Insert #Tselect 1001,'2011-09-11 09:00','2011-09-11 09:06',N'AAA' union allselect 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 #T2GoCreate table #T2([BILLID] int,[PID] nvarchar(5))Insert #T2select 1001,N'P1001' union allselect 1001,N'P1002' union allselect 1002,N'P1001' union allselect 1002,N'P1002'GOSELECT [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分钟*/
[解决办法]
------------------------------ 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 allselect 1002,'2011-09-11 09:00','2011-09-11 10:06','BBB' union allselect 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 allselect 1001,'P1002' union allselect 1002,'P1001' union allselect 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)ajoin (select BILLID,COUNT(1) as num from 明细表 group by BILLID)bon a.BILLID=b.BILLID----------------结果----------------------------/CREATER 花费 排名------- ---------------- --------------------BBB 33分钟 1AAA 13分钟 2(2 行受影响)*/
[解决办法]
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 行受影响)