怎么写每天前10名的SQL,高手进,在线等,急
我在写程序的时候,遇见个难题,就是需要从LOG表中取出每天前10名的记录,前提是,userid不能重复,也就这前10名里,只取每人当前最高的成绩~
表结构如下:
==========================
LOG表
CREATE TABLE [dbo].[T_LOG] (
[logId] [bigint] IDENTITY (1, 1) NOT NULL ,
[userId] [bigint] NOT NULL ,
[userResult] [int] NOT NULL ,
[logIsShow] [int] NOT NULL ,
[logMsg] [varchar] (2048) COLLATE Chinese_PRC_CI_AS NOT NULL ,[logIP] [varchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,[logInsertDate] [datetime] NOT NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
===================
用户表:
CREATE TABLE [dbo].[T_USER] (
[userId] [bigint] IDENTITY (1, 1) NOT NULL ,
[userName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[userCard] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[userCardType] [int] NOT NULL ,
[userPhone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[userIsShow] [smallint] NOT NULL ,
[userInsertDate] [datetime] NOT NULL ,
) ON [PRIMARY]
==========================
user表与log表的关系是1对多的关系,我现在就是想把每天的前10名取出来,分数一样的插入时间早的排在前面也就是logInsertDate这个字段~
请高手指点,谢谢了,在线等ing....
[解决办法]
select
t.*
from
T_LOG t
where
t.logId in(select
top 10 logId
from
T_LOG
where
datediff(dd,t.userInsertDate,userInsertDate)=0
order by
userResult desc,userInsertDate asc)
[解决办法]
T_LOG t
t是T_LOG的别名,其实也相当于T_LOG.*
用别名主要是为了区分开子语句的T_LOG
如果仔细观察这条语句会发现有两个
from T_LOG
from T_LOG
为了好区分它们就在第一个from T_LOG加了个别名t
[解决办法]
Declare @D Datetime
Set @D= '2007-08-01 '
Select Datediff(day,@D,Getdate()) As '@D与当前日期相差的天数 '
Select Datediff(month,@D,Getdate()) As '@D与当前日期相差的月数 '
Select Datediff(year,@D,Getdate()) As '@D与当前日期相差的年数 '
返回的结果是(第三个参数-第二个参数,第一个参数:指定哪一部分去计算)
[解决办法]
分类取前N个记录的SQL语句
有一个表AAA，结构如下：
类别编号 说明 排序
a aa 1
a aa2 2
a aa3 3
b bb 1
b bb2 2
b bb3 3
c cc 1
c cc2 2
c cc3 3
需要查询出来的结果是每个类别的头2条记录，按排序进行排序，结果如下：
类别编号 说明 排序
a aa 1
a aa2 2
b bb 1
b bb2 2
c cc 1
c cc2 2
谢谢各位了！只要测试通过马上给分！
if object_id( 'pubs..t1 ') is not null
drop table t1
go
create table t1(
类别编号 varchar(10),
说明 varchar(10),
排序 int
)
insert into t1(类别编号,说明,排序) values( 'a ', 'aa ',1)
insert into t1(类别编号,说明,排序) values( 'a ', 'aa2 ',2)
insert into t1(类别编号,说明,排序) values( 'a ', 'aa3 ',3)
insert into t1(类别编号,说明,排序) values( 'b ', 'bb ',1)
insert into t1(类别编号,说明,排序) values( 'b ', 'bb2 ',2)
insert into t1(类别编号,说明,排序) values( 'b ', 'bb3 ',3)
insert into t1(类别编号,说明,排序) values( 'c ', 'cc ',1)
insert into t1(类别编号,说明,排序) values( 'c ', 'cc2 ',2)
insert into t1(类别编号,说明,排序) values( 'c ', 'cc3 ',3)
select * from t1 as t
where (select count(*) from t1 where 类别编号 = t.类别编号 and 排序 < t.排序) < 2
drop table t1
类别编号 说明 排序
---------- ---------- -----------
a aa 1
a aa2 2
b bb 1
b bb2 2
c cc 1
c cc2 2
（所影响的行数为 6 行）
[解决办法]
1.先通过查询语句得出一个表
select .... into temp from LOG,T_USER where ...
2.获取每天的前十名.
select * from temp as t
where (select count(*) from temp where convert(varchar(10),userInsertDate,120) = convert(varchar(10),t.userInsertDate,120) and userResult > t.userResult) < 10
[解决办法]
select top 10 a.userid,userresult,loginsertdate
from t_log as A
where NOT EXISTS(SELECT * FROM t_log WHERE USERID=A.USERID AND USERRESULT> A.USERRESULT)
AND CONVERT(varchar(10),loginsertdate,120)=CONVERT(varchar(10),getdate(),120)
order by userresult desc,loginsertdate asc
这个是最简单的，LZ试下吧
[解决办法]
/*
Limpire：怎么会报错
*/
--原始数据：@T_LOG
declare @T_LOG table(logId int,userId int,userResult int,logIsShow int,logMsg varchar(18),logIP varchar(15),logInsertDate datetime)
insert @T_LOG
select 240,28,320,1, '210104198408231432 ', '192.168.162.106 ', '2007-9-5 9:20:28 ' union all
select 254,35,170,1, '110105198212052222 ', '192.168.162.104 ', '2007-9-5 10:24:11 ' union all
select 121,24,150,1, '21010419840823143x ', '192.168.162.106 ', '2007-8-27 14:13:38 ' union all
select 122,25,90,1, '11010519811002752x ', '192.168.162.104 ', '2007-8-27 14:13:48 ' union all
select 136,26,40,1, 'dfsdfsdfsd ', '192.168.162.104 ', '2007-8-27 14:22:34 ' union all
select 138,27,490,1, '432423 ', '192.168.162.104 ', '2007-8-27 14:23:39 ' union all
select 140,28,490,1, '210104198408231432 ', '192.168.162.106 ', '2007-8-27 14:26:26 ' union all
select 175,24,400,1, '21010419840823143x ', '192.168.162.106 ', '2007-8-27 15:03:04 ' union all
select 189,28,420,1, '210104198408231432 ', '192.168.162.106 ', '2007-8-27 15:23:58 ' union all
select 226,29,400,1, '234234 ', '192.168.162.104 ', '2007-8-27 16:49:31 ' union all
select 230,29,400,1, '234234 ', '192.168.162.104 ', '2007-8-28 16:49:31 ' union all
select 245,30,400,1, '111111 ', '192.168.162.104 ', '2007-8-27 16:49:31 ' union all
select 246,31,400,1, '222222 ', '192.168.162.104 ', '2007-8-27 12:49:31 ' union all
select 247,32,400,1, '111111 ', '192.168.162.104 ', '2007-8-27 16:49:31 ' union all
select 248,33,400,1, '111111 ', '192.168.162.104 ', '2007-8-27 16:49:31 ' union all
select 318,28,590,1, '210104198408231432 ', '192.168.162.106 ', '2007-9-7 10:44:01 ' union all
select 386,28,780,1, '210104198408231432 ', '192.168.162.106 ', '2007-9-7 12:20:29 '
--没有报错啊
select top 10 * from @T_LOG a where userResult = (select max(userResult) from @T_LOG where userId=a.userId) order by userResult desc
