主题：请教sql排名问题
jinjzk
[专家分：0] 发布于 2007-04-04 10:44:00
表名Aceshi
Callerno StartTime EndTime
13001086830 2007-1-16 15:21:15 2007-1-16 15:21:47
13001086830 2007-1-16 15:25:26 2007-1-16 15:27:57
13311581006 2007-1-23 13:54:04 2007-1-23 13:55:58
13095711119 2007-2-8 11:23:36 2007-2-8 11:23:45
13095711119 2007-2-8 11:20:18 2007-2-8 11:20:28
StartTime跟EndTime类型为datetime
SELECT SUM(CEILING(DATEDIFF(s, StartTime, EndTime) / 60) + 1) AS SUM FROM Aceshi where Callerno='13001086830' GROUP BY calleeid通过这语句可以将13001086830这个号码的时间总和取出来,我想根据这个时间总和来知道13001086830这个号码的排名,请教这个语句该怎么写
回复列表 （共3个回复）
沙发
jinjzk [专家分：0] 发布于 2007-04-04 11:08:00
在线等[em5]
板凳
潇洒老乌龟 [专家分：1050] 发布于 2007-04-08 11:01:00
if object_id('pubs..Aceshi') is not null
drop table Aceshi
go
create table Aceshi(Callerno varchar(11),StartTime datetime,EndTime datetime)
insert into Aceshi(Callerno,StartTime,EndTime) values('13001086830','2007-1-16 15:21:15','2007-1-16 15:21:47')
insert into Aceshi(Callerno,StartTime,EndTime) values('13001086830','2007-1-16 15:25:26','2007-1-16 15:27:57')
insert into Aceshi(Callerno,StartTime,EndTime) values('13311581006','2007-1-23 13:54:04','2007-1-23 13:55:58')
insert into Aceshi(Callerno,StartTime,EndTime) values('13095711119','2007-2-8 11:23:36' ,'2007-2-8 11:23:45')
insert into Aceshi(Callerno,StartTime,EndTime) values('13095711119','2007-2-8 11:20:18' ,'2007-2-8 11:20:28')
go
SELECT Callerno , SUM(DATEDIFF(s, StartTime, EndTime)) AS SUM into test FROM Aceshi GROUP BY Callerno
SELECT *,排名 = (SELECT COUNT(DISTINCT sum) FROM test WHERE sum >= n.sum) FROM test n ORDER BY 排名
drop table Aceshi ,test
/*
Callerno SUM 排名
----------- ----------- -----------
13001086830 183 1
13311581006 114 2
13095711119 19 3
（所影响的行数为 3 行）
*/
3 楼
潇洒老乌龟 [专家分：1050] 发布于 2007-04-08 11:03:00
if object_id('pubs..Aceshi') is not null
drop table Aceshi
go
create table Aceshi(Callerno varchar(11),StartTime datetime,EndTime datetime)
insert into Aceshi(Callerno,StartTime,EndTime) values('13001086830','2007-1-16 15:21:15','2007-1-16 15:21:47')
insert into Aceshi(Callerno,StartTime,EndTime) values('13001086830','2007-1-16 15:25:26','2007-1-16 15:27:57')
insert into Aceshi(Callerno,StartTime,EndTime) values('13311581006','2007-1-23 13:54:04','2007-1-23 13:55:58')
insert into Aceshi(Callerno,StartTime,EndTime) values('13095711119','2007-2-8 11:23:36' ,'2007-2-8 11:23:45')
insert into Aceshi(Callerno,StartTime,EndTime) values('13095711119','2007-2-8 11:20:18' ,'2007-2-8 11:20:28')
go
SELECT *,排名 = (SELECT COUNT(DISTINCT sum) FROM
(
SELECT Callerno , SUM(DATEDIFF(s, StartTime, EndTime)) AS SUM FROM Aceshi GROUP BY Callerno
) m
WHERE sum >= n.sum)
FROM
(
SELECT Callerno , SUM(DATEDIFF(s, StartTime, EndTime)) AS SUM FROM Aceshi GROUP BY Callerno
) n
ORDER BY 排名
drop table Aceshi
/*
Callerno SUM 排名
----------- ----------- -----------
13001086830 183 1
13311581006 114 2
13095711119 19 3
*/
我来回复
您所在位置：