从昨天开始在网上找查询代码 都不合适
如下
select UID,UScore,Utime from table a where UScore=(select max(UScore) from table where UID=a.UID) order by UScore
得到的是 所有时间 用户得分的排行榜
如果想查询每天的 应该怎么写,我加了时间范围进去,在order by UScore前面加入
and (Utime BETWEEN '2013/10/18' and '2013/10/19')
查询出的是上面总排行内 我所指定时间范围的一条数据,
也就是说 只有每个用户的最高得分,但不一定是我指定的时间范围内的最高分,
在指定时间范围内,数据库内还有其他用户信息,
写的有点啰嗦,现在脑子很乱,不知道该怎么办好,
请问各位高手,这个范围应该怎么写呢?
30 个解决方案
#1
日期筛选你自己加where条件即可
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-22 17:10:34
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([uid] int,[uscore] int,[utime] datetime)
insert [huang]
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5,7200,'2013-10-14 16:06:09' union all
select 6,16400,'2013-10-18 14:57:43' union all
select 6,16300,'2013-10-16 14:57:43' union all
select 5,7100,'2013-10-16 16:06:09' union all
select 3,11200,'2013-10-16 20:44:58' union all
select 6,16100,'2013-10-18 14:57:43' union all
select 1,8300,'2013-10-16 10:49:04'
--------------开始查询--------------------------
select *
from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT [uid],MAX([uscore])[uscore],CONVERT(CHAR(10),[utime],23) [utime] FROM huang GROUP BY [uid],CONVERT(CHAR(10),[utime],23))b
WHERE CONVERT(CHAR(10),a.[utime],23)=b.utime AND a.[uid]=b.[uid] AND a.uscore=b.uscore)
----------------结果----------------------------
/*
uid uscore utime
----------- ----------- -----------------------
1 8800 2013-10-12 10:49:04.000
3 11300 2013-10-15 20:44:58.000
5 7200 2013-10-14 16:06:09.000
6 16400 2013-10-18 14:57:43.000
6 16300 2013-10-16 14:57:43.000
5 7100 2013-10-16 16:06:09.000
3 11200 2013-10-16 20:44:58.000
1 8300 2013-10-16 10:49:04.000
*/
#2
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
#3
if object_id('Tempdb..#t') is not null drop table #t
create table #t(
[uid] int null,
[uscore] int null,
[utime] datetime null
)
Insert Into #t
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with cte as(
select * ,row_number() over(partition by [uid] order by [uscore] desc) rownumberid from #t
where (Utime BETWEEN '2013/10/18' and '2013/10/19')
)
select * from cte where rownumberid=1
----------------
(9 行受影响)
uid uscore utime rownumberid
----------- ----------- ----------------------- --------------------
6 16400 2013-10-18 14:57:43.000 1
(1 行受影响)
#4
120统计不了“一天”
#5
if object_id('[tb]') is not null drop table [huang]
go
create table [tb]([uid] int,[uscore] int,[utime] datetime)
insert [tb]
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5,7200,'2013-10-14 16:06:09' union all
select 6,16400,'2013-10-18 14:57:43' union all
select 6,16300,'2013-10-16 14:57:43' union all
select 5,7100,'2013-10-16 16:06:09' union all
select 3,11200,'2013-10-16 20:44:58' union all
select 6,16100,'2013-10-18 14:57:43' union all
select 1,8300,'2013-10-16 10:49:04'
SELECT CONVERT(VARCHAR(10),utime,120) AS utime,uid,MAX(uscore) AS uscore
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY CONVERT(VARCHAR(10),utime,120) ASC ,MAX(uscore) DESC
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
120统计不了“一天”
可以呢嘛。我一直都是用120
#9
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
120统计不了“一天”
可以呢嘛。我一直都是用120
那必须啊
,你还不是强制了
#11
每天的统计,
if object_id('Tempdb..#t') is not null drop table #t
create table #t(
[uid] int null,
[uscore] int null,
[utime] datetime null
)
Insert Into #t
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 3,11400,'2013-10-15 21:55:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16100,'2013-10-18 14:51:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with cte as(
select * ,row_number() over(partition by [uid],convert(varchar(10),utime,120) order by [uscore] desc) rownumberid from #t
)
select * from cte where rownumberid=1
-------------
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
--如果只需要某个时间段的,那么加上where就可以
--where (Utime BETWEEN '2013/10/18' and '2013/10/19')
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16400 2013-10-18 14:57:43.000 1
3 11300 2013-10-15 20:44:58.000 2
1 8800 2013-10-12 10:49:04.000 3
5 7200 2013-10-14 16:06:09.000 4
*/
#15
这个是,在某一天的排名情况,需要过滤时间:
if object_id('tb') is not null
drop table tb
go
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
#16
日期筛选你自己加where条件即可
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-22 17:10:34
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([uid] int,[uscore] int,[utime] datetime)
insert [huang]
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5,7200,'2013-10-14 16:06:09' union all
select 6,16400,'2013-10-18 14:57:43' union all
select 6,16300,'2013-10-16 14:57:43' union all
select 5,7100,'2013-10-16 16:06:09' union all
select 3,11200,'2013-10-16 20:44:58' union all
select 6,16100,'2013-10-18 14:57:43' union all
select 1,8300,'2013-10-16 10:49:04'
--------------开始查询--------------------------
select *
from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT [uid],MAX([uscore])[uscore],CONVERT(CHAR(10),[utime],23) [utime] FROM huang GROUP BY [uid],CONVERT(CHAR(10),[utime],23))b
WHERE CONVERT(CHAR(10),a.[utime],23)=b.utime AND a.[uid]=b.[uid] AND a.uscore=b.uscore)
----------------结果----------------------------
/*
uid uscore utime
----------- ----------- -----------------------
1 8800 2013-10-12 10:49:04.000
3 11300 2013-10-15 20:44:58.000
5 7200 2013-10-14 16:06:09.000
6 16400 2013-10-18 14:57:43.000
6 16300 2013-10-16 14:57:43.000
5 7100 2013-10-16 16:06:09.000
3 11200 2013-10-16 20:44:58.000
1 8300 2013-10-16 10:49:04.000
*/
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-22 17:10:34
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([uid] int,[uscore] int,[utime] datetime)
insert [huang]
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5,7200,'2013-10-14 16:06:09' union all
select 6,16400,'2013-10-18 14:57:43' union all
select 6,16300,'2013-10-16 14:57:43' union all
select 5,7100,'2013-10-16 16:06:09' union all
select 3,11200,'2013-10-16 20:44:58' union all
select 6,16100,'2013-10-18 14:57:43' union all
select 1,8300,'2013-10-16 10:49:04'
--------------开始查询--------------------------
select *
from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT [uid],MAX([uscore])[uscore] FROM huang --如果需要筛选日期,加where条件在这里
GROUP BY [uid])b
WHERE a.[uid]=b.[uid] AND a.uscore=b.uscore)
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-22 17:10:34
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([uid] int,[uscore] int,[utime] datetime)
insert [huang]
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5,7200,'2013-10-14 16:06:09' union all
select 6,16400,'2013-10-18 14:57:43' union all
select 6,16300,'2013-10-16 14:57:43' union all
select 5,7100,'2013-10-16 16:06:09' union all
select 3,11200,'2013-10-16 20:44:58' union all
select 6,16100,'2013-10-18 14:57:43' union all
select 1,8300,'2013-10-16 10:49:04'
--------------开始查询--------------------------
select *
from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT [uid],MAX([uscore])[uscore] FROM huang --如果需要筛选日期,加where条件在这里
GROUP BY [uid])b
WHERE a.[uid]=b.[uid] AND a.uscore=b.uscore)
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t where rownum = 1
#24
这个是,在某一天的排名情况,需要过滤时间:
if object_id('tb') is not null
drop table tb
go
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t where rownum = 1
好像放在asp的查询里没法运行,一运行iis就死了
#25
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-22 17:10:34
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([uid] int,[uscore] int,[utime] datetime)
insert [huang]
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5,7200,'2013-10-14 16:06:09' union all
select 6,16400,'2013-10-18 14:57:43' union all
select 6,16300,'2013-10-16 14:57:43' union all
select 5,7100,'2013-10-16 16:06:09' union all
select 3,11200,'2013-10-16 20:44:58' union all
select 6,16100,'2013-10-18 14:57:43' union all
select 1,8300,'2013-10-16 10:49:04'
--------------开始查询--------------------------
select *
from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT [uid],MAX([uscore])[uscore] FROM huang --如果需要筛选日期,加where条件在这里
GROUP BY [uid])b
WHERE a.[uid]=b.[uid] AND a.uscore=b.uscore)
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t where rownum = 1
好像放在asp的查询里没法运行,一运行iis就死了
我把语句改写了一下,试试:
select [uid],uscore,utime,dense_rank() over(order by uscore desc) as r
from
(
select [uid],uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
#27
改了一下:
select uid,uscore,utime,dense_rank() over(order by uscore desc) as r
from
(
select uid,uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
#28
改了一下:
select uid,uscore,utime,dense_rank() over(order by uscore desc) as r
from
(
select uid,uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
select uid,uscore,utime,dense_rank() over(order by uscore desc) as r
from
(
select uid,uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
select uid,uscore,utime,row_number() over(order by uscore desc) as r
from
(
select uid,uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
#30
改了一下:
select uid,uscore,utime,dense_rank() over(order by uscore desc) as r
from
(
select uid,uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
select uid,uscore,utime,row_number() over(order by uscore desc) as r
from
(
select uid,uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
通过测试,太给力了,非常谢谢,兄弟
#1
日期筛选你自己加where条件即可
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-22 17:10:34
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([uid] int,[uscore] int,[utime] datetime)
insert [huang]
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5,7200,'2013-10-14 16:06:09' union all
select 6,16400,'2013-10-18 14:57:43' union all
select 6,16300,'2013-10-16 14:57:43' union all
select 5,7100,'2013-10-16 16:06:09' union all
select 3,11200,'2013-10-16 20:44:58' union all
select 6,16100,'2013-10-18 14:57:43' union all
select 1,8300,'2013-10-16 10:49:04'
--------------开始查询--------------------------
select *
from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT [uid],MAX([uscore])[uscore],CONVERT(CHAR(10),[utime],23) [utime] FROM huang GROUP BY [uid],CONVERT(CHAR(10),[utime],23))b
WHERE CONVERT(CHAR(10),a.[utime],23)=b.utime AND a.[uid]=b.[uid] AND a.uscore=b.uscore)
----------------结果----------------------------
/*
uid uscore utime
----------- ----------- -----------------------
1 8800 2013-10-12 10:49:04.000
3 11300 2013-10-15 20:44:58.000
5 7200 2013-10-14 16:06:09.000
6 16400 2013-10-18 14:57:43.000
6 16300 2013-10-16 14:57:43.000
5 7100 2013-10-16 16:06:09.000
3 11200 2013-10-16 20:44:58.000
1 8300 2013-10-16 10:49:04.000
*/
#2
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
#3
if object_id('Tempdb..#t') is not null drop table #t
create table #t(
[uid] int null,
[uscore] int null,
[utime] datetime null
)
Insert Into #t
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with cte as(
select * ,row_number() over(partition by [uid] order by [uscore] desc) rownumberid from #t
where (Utime BETWEEN '2013/10/18' and '2013/10/19')
)
select * from cte where rownumberid=1
----------------
(9 行受影响)
uid uscore utime rownumberid
----------- ----------- ----------------------- --------------------
6 16400 2013-10-18 14:57:43.000 1
(1 行受影响)
#4
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
120统计不了“一天”
#5
if object_id('[tb]') is not null drop table [huang]
go
create table [tb]([uid] int,[uscore] int,[utime] datetime)
insert [tb]
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5,7200,'2013-10-14 16:06:09' union all
select 6,16400,'2013-10-18 14:57:43' union all
select 6,16300,'2013-10-16 14:57:43' union all
select 5,7100,'2013-10-16 16:06:09' union all
select 3,11200,'2013-10-16 20:44:58' union all
select 6,16100,'2013-10-18 14:57:43' union all
select 1,8300,'2013-10-16 10:49:04'
SELECT CONVERT(VARCHAR(10),utime,120) AS utime,uid,MAX(uscore) AS uscore
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY CONVERT(VARCHAR(10),utime,120) ASC ,MAX(uscore) DESC
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
120统计不了“一天”
可以呢嘛。我一直都是用120
#8
那是因为你强制指定varchar(10)
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
120统计不了“一天”
可以呢嘛。我一直都是用120
#9
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
120统计不了“一天”
可以呢嘛。我一直都是用120
那必须啊
,你还不是强制了
#11
每天的统计,
if object_id('Tempdb..#t') is not null drop table #t
create table #t(
[uid] int null,
[uscore] int null,
[utime] datetime null
)
Insert Into #t
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 3,11400,'2013-10-15 21:55:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16100,'2013-10-18 14:51:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with cte as(
select * ,row_number() over(partition by [uid],convert(varchar(10),utime,120) order by [uscore] desc) rownumberid from #t
)
select * from cte where rownumberid=1
-------------
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
--如果只需要某个时间段的,那么加上where就可以
--where (Utime BETWEEN '2013/10/18' and '2013/10/19')
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16400 2013-10-18 14:57:43.000 1
3 11300 2013-10-15 20:44:58.000 2
1 8800 2013-10-12 10:49:04.000 3
5 7200 2013-10-14 16:06:09.000 4
*/
#15
这个是,在某一天的排名情况,需要过滤时间:
if object_id('tb') is not null
drop table tb
go
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
#16
日期筛选你自己加where条件即可
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-22 17:10:34
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([uid] int,[uscore] int,[utime] datetime)
insert [huang]
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5,7200,'2013-10-14 16:06:09' union all
select 6,16400,'2013-10-18 14:57:43' union all
select 6,16300,'2013-10-16 14:57:43' union all
select 5,7100,'2013-10-16 16:06:09' union all
select 3,11200,'2013-10-16 20:44:58' union all
select 6,16100,'2013-10-18 14:57:43' union all
select 1,8300,'2013-10-16 10:49:04'
--------------开始查询--------------------------
select *
from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT [uid],MAX([uscore])[uscore],CONVERT(CHAR(10),[utime],23) [utime] FROM huang GROUP BY [uid],CONVERT(CHAR(10),[utime],23))b
WHERE CONVERT(CHAR(10),a.[utime],23)=b.utime AND a.[uid]=b.[uid] AND a.uscore=b.uscore)
----------------结果----------------------------
/*
uid uscore utime
----------- ----------- -----------------------
1 8800 2013-10-12 10:49:04.000
3 11300 2013-10-15 20:44:58.000
5 7200 2013-10-14 16:06:09.000
6 16400 2013-10-18 14:57:43.000
6 16300 2013-10-16 14:57:43.000
5 7100 2013-10-16 16:06:09.000
3 11200 2013-10-16 20:44:58.000
1 8300 2013-10-16 10:49:04.000
*/
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
SELECT uid,MAX(uscore) AS uscore,CONVERT(VARCHAR(10),utime,120) AS utime
FROM tb WITH(NOLOCK)
GROUP BY uid,CONVERT(VARCHAR(10),utime,120)
ORDER BY MAX(uscore) DESC
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-22 17:10:34
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([uid] int,[uscore] int,[utime] datetime)
insert [huang]
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5,7200,'2013-10-14 16:06:09' union all
select 6,16400,'2013-10-18 14:57:43' union all
select 6,16300,'2013-10-16 14:57:43' union all
select 5,7100,'2013-10-16 16:06:09' union all
select 3,11200,'2013-10-16 20:44:58' union all
select 6,16100,'2013-10-18 14:57:43' union all
select 1,8300,'2013-10-16 10:49:04'
--------------开始查询--------------------------
select *
from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT [uid],MAX([uscore])[uscore] FROM huang --如果需要筛选日期,加where条件在这里
GROUP BY [uid])b
WHERE a.[uid]=b.[uid] AND a.uscore=b.uscore)
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-22 17:10:34
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([uid] int,[uscore] int,[utime] datetime)
insert [huang]
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5,7200,'2013-10-14 16:06:09' union all
select 6,16400,'2013-10-18 14:57:43' union all
select 6,16300,'2013-10-16 14:57:43' union all
select 5,7100,'2013-10-16 16:06:09' union all
select 3,11200,'2013-10-16 20:44:58' union all
select 6,16100,'2013-10-18 14:57:43' union all
select 1,8300,'2013-10-16 10:49:04'
--------------开始查询--------------------------
select *
from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT [uid],MAX([uscore])[uscore] FROM huang --如果需要筛选日期,加where条件在这里
GROUP BY [uid])b
WHERE a.[uid]=b.[uid] AND a.uscore=b.uscore)
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t where rownum = 1
#24
这个是,在某一天的排名情况,需要过滤时间:
if object_id('tb') is not null
drop table tb
go
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t where rownum = 1
好像放在asp的查询里没法运行,一运行iis就死了
#25
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-22 17:10:34
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([uid] int,[uscore] int,[utime] datetime)
insert [huang]
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5,7200,'2013-10-14 16:06:09' union all
select 6,16400,'2013-10-18 14:57:43' union all
select 6,16300,'2013-10-16 14:57:43' union all
select 5,7100,'2013-10-16 16:06:09' union all
select 3,11200,'2013-10-16 20:44:58' union all
select 6,16100,'2013-10-18 14:57:43' union all
select 1,8300,'2013-10-16 10:49:04'
--------------开始查询--------------------------
select *
from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT [uid],MAX([uscore])[uscore] FROM huang --如果需要筛选日期,加where条件在这里
GROUP BY [uid])b
WHERE a.[uid]=b.[uid] AND a.uscore=b.uscore)
create table tb
(
uid int null,
uscore int null,
utime datetime null
)
Insert Into tb
select 1,8800,'2013-10-12 10:49:04' union all
select 3,11300,'2013-10-15 20:44:58' union all
select 5 ,7200,'2013-10-14 16:06:09' union all
select 6 ,16400,'2013-10-18 14:57:43' union all
select 6 ,16300,'2013-10-16 14:57:43' union all
select 5 ,7100,'2013-10-16 16:06:09' union all
select 3 ,11200,'2013-10-16 20:44:58' union all
select 6 ,16100,'2013-10-18 14:57:43' union all
select 1 ,8300,'2013-10-16 10:49:04'
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t
where rownum = 1
/*
uid uscore utime 排名
----------- ----------- ----------------------- --------------------
6 16300 2013-10-16 14:57:43.000 1
3 11200 2013-10-16 20:44:58.000 2
1 8300 2013-10-16 10:49:04.000 3
5 7100 2013-10-16 16:06:09.000 4
*/
;with t
as
(
select uid,
uscore,
utime,
row_number() over(partition by uid
order by uscore desc) as rownum
from tb
where utime >= '2013-10-16' and
utime < '2013-10-17'
)
select uid,
uscore,
utime,
dense_rank() over(order by uscore desc) as '排名'
from t where rownum = 1
好像放在asp的查询里没法运行,一运行iis就死了
我把语句改写了一下,试试:
select [uid],uscore,utime,dense_rank() over(order by uscore desc) as r
from
(
select [uid],uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
#27
改了一下:
select uid,uscore,utime,dense_rank() over(order by uscore desc) as r
from
(
select uid,uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
#28
改了一下:
select uid,uscore,utime,dense_rank() over(order by uscore desc) as r
from
(
select uid,uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
select uid,uscore,utime,dense_rank() over(order by uscore desc) as r
from
(
select uid,uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
select uid,uscore,utime,row_number() over(order by uscore desc) as r
from
(
select uid,uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
#30
改了一下:
select uid,uscore,utime,dense_rank() over(order by uscore desc) as r
from
(
select uid,uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1
select uid,uscore,utime,row_number() over(order by uscore desc) as r
from
(
select uid,uscore,utime,row_number() over(partition by uid order by uscore desc) as rownum
from tb where utime >= '2013-10-16' and utime < '2013-10-17'
)t where rownum = 1