分组统计（在线等）
我有一张表a
id name tel departTime
111222012-07-17 00:00:00
8abc2012-07-17 00:00:00
9ab1232012-07-17 00:00:00
10ab123 2012-07-18 00:00:00
11ab1232012-06-13 00:00:00

我的要求是：
根据name和tel进行分组 ，(分组时时间最好是从小到大排，比较时是组内第二条时间和第一条时间比，后面同样)还要就是要对应每组内时间相比较，得出平均值
时间比较只要比较天数就行了

最后结果是

name tel avg（平均天数）
11 22 0
ab c 0
ab 123 11（11左右，第一次是0613和0717比=34，第二次是0717和0718比=1，再求平均值）

这个sql语句如何写

[解决办法]

SQL code

if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([id] int,[name] varchar(2),[tel] varchar(3),[departTime] datetime)insert [tb]select 1,'11','22','2012-07-17 00:00:00' union allselect 8,'ab','c','2012-07-17 00:00:00' union allselect 9,'ab','123','2012-07-17 00:00:00' union allselect 10,'ab','123','2012-07-18 00:00:00' union allselect 11,'ab','123','2012-06-13 00:00:00'goselect name,tel,avg(dt) as [avg]from(select *,datediff(dd,departtime,isnull((select top 1 departtime from tb where name=t.name and tel=t.tel and departtime>t.departtime order by departtime),departtime)) as dtfrom tb t) tgroup by name,tel/**name tel  avg---- ---- -----------ab   123  1111   22   0ab   c    0(3 行受影响)**/
[解决办法]
SQL code
CREATE TABLE #TEMP(ID INT,NAME VARCHAR(30),TEL VARCHAR(30), DEPARTTIME DATETIME)GOINSERT INTO #TEMPSELECT 1, '11', '22', '2012-07-17 00:00:00' UNION ALLSELECT 8 ,'AB', 'C' ,'2012-07-17 00:00:00' UNION ALLSELECT 9, 'AB', '123' ,'2012-07-17 00:00:00' UNION ALLSELECT 10, 'AB', '123', '2012-07-18 00:00:00' UNION ALLSELECT 11, 'AB', '123', '2012-06-13 00:00:00' GOSELECT NAME,TEL,AVG(DT) AS [AVG]FROM(SELECT ID,NAME,TEL,DATEDIFF(DD,DEPARTTIME,ISNULL((SELECT TOP 1 DEPARTTIME FROM #TEMP WHERE NAME=T.NAME AND TEL=T.TEL AND ID != T.ID AND DEPARTTIME>=T.DEPARTTIME ORDER BY DEPARTTIME),DEPARTTIME)) DTFROM #TEMP T) TGROUP BY NAME,TELORDER BY MIN(ID)DROP TABLE #TEMPGO-----------------------11    22    0AB    C    0AB    123    11
[解决办法]
SQL code
--> 测试数据：[A1]if object_id('[A1]') is not null drop table [A1]create table [A1]([id] int,[name] varchar(2),[tel] varchar(3),[departTime] datetime)goinsert [A1]select 1,'11','22','2012-07-17 00:00:00' union allselect 8,'ab','c','2012-07-17 00:00:00' union allselect 9,'ab','123','2012-07-17 00:00:00' union allselect 10,'ab','123','2012-07-18 00:00:00' union allselect 11,'ab','123','2012-06-13 00:00:00'go;with tas(select     px=ROW_NUMBER()over(partition by [name],[tel] order by [departTime] asc),    *from    [A1]),m as(select     px,    name,    tel,    [departTime],    0 as TotalDaysfrom     twhere     px=1union allselect     a.px,    a.name,    a.tel,    a.[departTime],    DATEDIFF(dd,m.[departTime],a.departTime)from     t ainner join     mon     a.px=m.px+1 and a.tel=m.tel)--select * from mselect     name,    tel,    AVG(TotalDays) as TotalDays from     m group by    name,tel/*name    tel    TotalDays-----------------------------------ab    123    1111    22    0ab    c    0*/