SELECT DISTINCT top 9 userID FROM table
可以搜前9條,但我無法以adddate排序,一用adddate排序就出錯
怎么解決?
可以搜前9條,但我無法以adddate排序,一用adddate排序就出錯
怎么解決?
解决方案 »
- 将 varchar 转换为数据类型 numeric 时发生算术溢出错误
- SQL 2005 Managemnet能够连接到远程数据库吗?
- 事务无法启动,是什么原因?
- 求如何锁定插入操作,生成以年份为基础的自动增长列?
- 排序问题
- 能用事务来进行操作吗?比如一种操作,在网页上要通过填写六张网页,才完成一种操作过程,如果成功能全部提交,不行全部撒消
- 如何把Access内的表格completely转移到SQL Server 2000的数据库里?
- 用EXECUTE生成一个动态临时表之后,接下来查询数据的时候找不到这个临时表。求救!!
- 一个系统?请报价
- 哪位大虾了解informix复制服务器问题,帮忙啊,请详细说说!
- SQL7.0升级问题? 我能够改变SQL数据库的参数解决这个问题吗?
- 多表插入的问题
where not exists(
select 1 from 表 where userID=a.userID and adddate<>a.adddate)
order by adddate desc
不好意思,各位,刚才搞错了,
這樣不行啊,重復的記錄都出來了,
----------------
to zjcxc(邹建)
這樣也不行啊,userID重復的記錄都不出來了,
如userID為:
8
8
1
3
3
5
就只搜出1和5,而我是要8,1,3,5這樣的記錄啊
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~这可是你说的,只要不重复的
from 表 a
where exists(
select 1 from(
select top 1 userID,adddate
from 表
where userID=a.userID
order by abs(datediff(second,adddate ,getdate())) desc
)aa where userID=a.userID and adddate<>a.adddate)
where not exists(
select 1 from 表 where userID=a.userID and adddate<>a.adddate)
order by adddate desc如果是mysql用
select distinct * from table1 order by adddate desc limit 9
from tb group by userid
order max(adddate) desc
select top 9 userid,adddate=max(adddate)
from tb group by userid
order max(adddate) desc
--如果还要包含其他字段:
select a.*
from tb a,(
select top 9 userid,adddate=max(adddate)
from tb group by userid
order max(adddate) desc
)b where a.userid=b.userid and a.adddate=b.adddate
where adddate in (
select adddate
from 表 a
where exists(
select 1 from(
select top 1 userID,adddate
from 表
where userID=a.userID
order by abs(datediff(second,adddate ,getdate())) desc
)aa where userID=a.userID and adddate<>a.adddate))
create table tb(userID varchar(10),adddate datetime,strl varchar(10))
insert tb values('zhangzs','2004-08-12','1')
insert tb values('zhangzs','2004-08-13','q')
insert tb values('hongyl','2004-08-14','w')
insert tb values('hongyl','2004-08-15','w')
insert tb values('guoy','2004-08-16','qw')
insert tb values('guoy','2004-08-17','qwe')
select top 2 * from tb
where adddate in (
select adddate
from tb a
where exists(
select 1 from(
select top 1 userID,adddate
from tb
where userID=a.userID
order by abs(datediff(second,adddate ,getdate())) desc
)aa where userID=a.userID and adddate<>a.adddate))
drop table tb---运行结果userID adddate strl
---------- ------------------------------------------------------ ----------
zhangzs 2004-08-13 00:00:00.000 q
hongyl 2004-08-14 00:00:00.000 w
--注意:把top 2 该成top 9 就可以了,试试。包括其他字段
不行啊,沒重復的都不出來了,如只出了8和3再測試別的朋友的看看
select a.*
from 表 a,(
select top 9 userid,adddate=max(adddate)
from 表 group by userid
order by max(adddate) desc
)b where a.userid=b.userid and a.adddate=b.adddate
insert tb values('zhangzs','2004-08-12','1')
insert tb values('zhangzs','2004-08-13','q')
insert tb values('hongyl','2004-08-14','w')
insert tb values('hongyl','2004-08-15','w')
insert tb values('guoy','2004-08-16','qw')
insert tb values('guoy','2004-08-17','qwe')
insert tb values('aa','2004-08-17','qwe')select a.*
from tb a,(
select top 9 userid,adddate=max(adddate)
from tb group by userid
order by max(adddate) desc
)b where a.userid=b.userid and a.adddate=b.adddatedrop table tb--userID adddate strl
---------- ------------------------------------------------------ ----------
zhangzs 2004-08-13 00:00:00.000 q
hongyl 2004-08-15 00:00:00.000 w
guoy 2004-08-17 00:00:00.000 qwe
aa 2004-08-17 00:00:00.000 qwe
select userid,adddate=max(adddate)
from tb group by userid
order max(adddate) desc
成功通過測試,
非常謝謝
再測試一下別人的看看
學了不少東西,以前在別的論壇也問過這問題沒解決,在這一下就解決了
多种理解!
是不是就相当于:每个人的最后登录时间?
zjcxc(邹建) 就是这意思!
BILLSJONE (BILL_JONE) 你是不是这意思?