select * from tbl as a where login_datetime in ( select top 5 login_datetime from tbl where a.operator_name=operator_name order by login_datetime desc)
select * from table t where ID in(select top 5 ID from table where operator_name=t.operator_name order by login_datetime desc)
select * from 表 t where login_datetime in (select distinct top 5 login_datetime from 表 order by login_datetime desc) order by operator_name,login_datetime desc
我都试过,不行, select * from tbl as a where login_datetime in ( select top 5 login_datetime from tbl where a.operator_name=operator_name order by login_datetime desc) 结果是: allen 。。 allen cxq cxq cxq allen allen allen select * from table t where ID in(select top 5 ID from table where operator_name=t.operator_name order by login_datetime desc)结果是: allen 。 allen cxq cxq cxq allen allen allen
select * from 表 t where login_datetime in (select distinct top 5 login_datetime from 表 order by login_datetime desc) order by operator_name,login_datetime desc 结果是:、 allen allen cxq cxq cxq 以上都是列出的operator_name,别的字段都省略了
试一下 select distinct a.operator_name,a.login_datetime from 表 a where login_datetime in (select top 5 login_datetime from (select top 100 percent operator_name,login_datetime from 表 group by operator_name,login_datetime order by operator_name,login_datetime desc) b where b.operator_name=a.operator_name) order by a.operator_name,a.login_datetime desc问题可能在于你的登录日期中含有时间部分,1天中可能有很多次登,出来的结果就有很多条记录了
--试试这个应该可以 Select * from tbl as a where convert(varchar(30),login_datetime,121)+ cast(id as varchar(10)) in ( select top 5 convert(varchar(30), login_datetime,121)+cast(id as varchar(10)) from tbl where a.operator_name=operator_name order by login_datetime desc) order by a.operator_name
select * from table where id in (select a.id from (select top 5 id,operator_name from table group by operator_name,id) as a ) order by login_datetime desc 楼主你试试这个!
Select * from tbl as a where convert(varchar(30),login_datetime,121)+ cast(id as varchar(10)) in ( select top 5 convert(varchar(30), login_datetime,121)+cast(id as varchar(10)) from tbl where a.operator_name=operator_name order by login_datetime desc) order by a.operator_nameWangZWang(阿来) 的这个很接近了,不过还有点问题,纪录不是按照时间逆序排列的,其他的都符合。 下面是查询出来的纪录 id operator_name login_datetime103 allen 2005-09-05 09:33:45.000 104 allen 2005-09-05 10:21:59.000 115 allen 2005-09-06 08:41:00.000 102 allen 2005-09-02 16:59:33.000 116 allen 2005-09-06 08:48:38.000 105 cxq 2005-09-05 13:57:30.000 106 cxq 2005-09-05 13:58:25.000 107 cxq 2005-09-05 13:58:40.000 110 sml 2005-09-05 14:55:32.000 111 sml 2005-09-05 14:55:44.000 112 sml 2005-09-05 14:55:51.000 113 sml 2005-09-05 14:56:00.000 114 sml 2005-09-05 14:56:06.000
select login_datetime,operator_name from table1 group by login_datetime,operator having count(1)<5 order by operator_name desc这样是你要的结果吗?
To: xxljd(闪) ( ) 我想要的结果每个用户名取5条纪录,这5条纪录按照时间逆序排列,如果没有5条纪录,就有几条就取几条,上面的 cxq 就是这样
在阿来的排序后面再加个条件就行了 Select * from tbl as a where convert(varchar(30),login_datetime,121)+ cast(id as varchar(10)) in ( select top 5 convert(varchar(30), login_datetime,121)+cast(id as varchar(10)) from tbl where a.operator_name=operator_name order by login_datetime desc) order by a.operator_name, a.datetime
shingkong(千年虫)正确,结帖子拉!~谢谢!
Select a.operator_name,a.login_datetime from table1 A Where Not Exists (Select 1 from table1 Where operator_name=A.operator_name And login_datetime>=A.login_datetime Having Count(1)>5) Order By operator_name,login_datetime 这个试看看我调试了 应该可以
where login_datetime in (
select top 5 login_datetime from tbl
where a.operator_name=operator_name
order by login_datetime desc)
where ID
in(select top 5 ID from table where operator_name=t.operator_name order by login_datetime desc)
where login_datetime in (select distinct top 5 login_datetime
from 表
order by login_datetime desc)
order by operator_name,login_datetime desc
select * from tbl as a
where login_datetime in (
select top 5 login_datetime from tbl
where a.operator_name=operator_name
order by login_datetime desc)
结果是:
allen 。。
allen
cxq
cxq
cxq
allen
allen
allen select * from table t
where ID
in(select top 5 ID from table where operator_name=t.operator_name order by login_datetime desc)结果是:
allen 。
allen
cxq
cxq
cxq
allen
allen
allen
select * from 表 t
where login_datetime in (select distinct top 5 login_datetime
from 表
order by login_datetime desc)
order by operator_name,login_datetime desc
结果是:、
allen
allen
cxq
cxq
cxq 以上都是列出的operator_name,别的字段都省略了
select distinct a.operator_name,a.login_datetime from 表 a where login_datetime in
(select top 5 login_datetime from (select top 100 percent operator_name,login_datetime from 表 group by operator_name,login_datetime order by operator_name,login_datetime desc) b where b.operator_name=a.operator_name)
order by a.operator_name,a.login_datetime desc问题可能在于你的登录日期中含有时间部分,1天中可能有很多次登,出来的结果就有很多条记录了
Select * from tbl as a
where convert(varchar(30),login_datetime,121)+
cast(id as varchar(10)) in (
select top 5 convert(varchar(30),
login_datetime,121)+cast(id as varchar(10))
from tbl
where a.operator_name=operator_name
order by login_datetime desc)
order by a.operator_name
如果是字符型,要先转换成datetime类型
时间字段是datetime型的
(select a.id from (select top 5 id,operator_name from table group by operator_name,id)
as a )
order by login_datetime desc
楼主你试试这个!
where convert(varchar(30),login_datetime,121)+
cast(id as varchar(10)) in (
select top 5 convert(varchar(30),
login_datetime,121)+cast(id as varchar(10))
from tbl
where a.operator_name=operator_name
order by login_datetime desc)
order by a.operator_nameWangZWang(阿来) 的这个很接近了,不过还有点问题,纪录不是按照时间逆序排列的,其他的都符合。
下面是查询出来的纪录
id operator_name login_datetime103 allen 2005-09-05 09:33:45.000
104 allen 2005-09-05 10:21:59.000
115 allen 2005-09-06 08:41:00.000
102 allen 2005-09-02 16:59:33.000
116 allen 2005-09-06 08:48:38.000
105 cxq 2005-09-05 13:57:30.000
106 cxq 2005-09-05 13:58:25.000
107 cxq 2005-09-05 13:58:40.000
110 sml 2005-09-05 14:55:32.000
111 sml 2005-09-05 14:55:44.000
112 sml 2005-09-05 14:55:51.000
113 sml 2005-09-05 14:56:00.000
114 sml 2005-09-05 14:56:06.000
group by login_datetime,operator
having count(1)<5
order by operator_name desc这样是你要的结果吗?
我想要的结果每个用户名取5条纪录,这5条纪录按照时间逆序排列,如果没有5条纪录,就有几条就取几条,上面的 cxq 就是这样
Select * from tbl as a
where convert(varchar(30),login_datetime,121)+
cast(id as varchar(10)) in (
select top 5 convert(varchar(30),
login_datetime,121)+cast(id as varchar(10))
from tbl
where a.operator_name=operator_name
order by login_datetime desc)
order by a.operator_name, a.datetime
Select a.operator_name,a.login_datetime from table1 A
Where Not Exists (Select 1 from table1 Where operator_name=A.operator_name
And login_datetime>=A.login_datetime Having Count(1)>5)
Order By operator_name,login_datetime
这个试看看我调试了 应该可以
你的也可以,谢谢你!!!