简单问题...取ID

[xiaoyuehen]

设表A有如下记录:
ID USERID TDATE TYPE
1 1 2000-11-11 10:12:12 A
2 1 2003-12-11 10:12:12 B
3 1 2002-10-20 10:12:12 C
4 2 2001-10-23 10:12:12 A
5 2 2000-10-18 10:12:12 B
6 2 2003-12-25 10:12:12 C
7 3 2001-11-23 10:12:12 A
8 3 2000-12-18 10:12:12 B
9 3 2003-10-25 10:12:12 C
10 3 2003-10-25 19:12:12 D


用如下语句可以得到结果:
select userid, max([tdate]) as rq from a group by userid

userid rq
1 2003-12-11 10:12:12
2 2003-12-25 10:12:12
3 2003-10-25 19:12:12

问题如下:

现在我想取得对应的max([tdate])下记录id号是多少, 如上结果应该对应id分别为:2,6,10

语句应怎么写?
(注:select * from a inner join (select userid, max([tdate]) as rq from a group by userid) as b on a.userid = b.userid and a.tdate=b.rq ORDER BY A.userid 这句行不通...会将 id 为 9 的记录也选出来)

[txlicenhe]

select * from rq a
where tdate = (select max(tdate) from rq where userid = a.userid)

[xiaoyuehen]

不行呀....有重复数据..即日期同在那一天的, 但小时不一样..把 id 为 9 的记录也选出来了..

[jyk1970]

select *
from 表A a,
(select userid, max([tdate]) as rq from a group by userid) as x
where a.userid=x.userid and a.tdate=x.rq

[xiaoyuehen]

楼上的语句我的注里面那句效果是一样的...9的记录也被选出来了...

[xiaoyuehen]

暂时解决, 语句如下:
SELECT * FROM A b WHERE id in (SELECT top 1 id FROM a WHERE userid=b.userid order by tdate) order by userid

执行速度很慢!!:(

[jyk1970]

create table [表A]
(
id int not null,
userid int not null,
tdate datetime not null,
type char(1) not null
)

insert into [表A] values (1, 1, '2000-11-11 10:12:12', 'A')
insert into [表A] values (2, 1, '2003-12-11 10:12:12', 'B')
insert into [表A] values (3, 1, '2002-10-20 10:12:12', 'C')
insert into [表A] values (4, 2, '2001-10-23 10:12:12', 'A')
insert into [表A] values (5, 2, '2000-10-18 10:12:12', 'B')
insert into [表A] values (6, 2, '2003-12-25 10:12:12', 'C')
insert into [表A] values (7, 3, '2001-11-23 10:12:12', 'A')
insert into [表A] values (8, 3, '2000-12-18 10:12:12', 'B')
insert into [表A] values (9, 3, '2003-10-25 10:12:12', 'C')
insert into [表A] values (10, 3, '2003-10-25 19:12:12', 'D')
go

--方法
select a.*
from [表A] a,
(select userid, max([tdate]) as rq from [表A] group by userid) as x
where a.userid=x.userid and a.tdate=x.rq
order by a.userid

--结果
id userid tdate type
----------- ----------- ------------------------------------------------------ ----
2 1 2003-12-11 10:12:12.000 B
6 2 2003-12-25 10:12:12.000 C
10 3 2003-10-25 19:12:12.000 D

[zjcxc]

--这样查询吧
select *
from 表A a join(
select userid,tdate=max(tdate) from 表A group by userid
) b on a.userid=b.userid and a.tdate=b.tdate
order by id

[zjcxc]

--上面的字段多了,改一下:
select a.*
from 表A a join(
select userid,tdate=max(tdate) from 表A group by userid
) b on a.userid=b.userid and a.tdate=b.tdate
order by id

[zjcxc]

--下面是测试数据
declare @表A table(id int,userid int,tdate datetime, type char(1))
insert into @表A
select 1,1,'2000-11-11 10:12:12','A'
union all select 2,1,'2003-12-11 10:12:12','B'
union all select 3,1,'2002-10-20 10:12:12','C'
union all select 4,2,'2001-10-23 10:12:12','A'
union all select 5,2,'2000-10-18 10:12:12','B'
union all select 6,2,'2003-12-25 10:12:12','C'
union all select 7,3,'2001-11-23 10:12:12','A'
union all select 8,3,'2000-12-18 10:12:12','B'
union all select 9,3,'2003-10-25 10:12:12','C'
union all select 10,3,'2003-10-25 19:12:12','D'

--查询
select a.*
from @表A a join(
select userid,tdate=max(tdate) from @表A group by userid
) b on a.userid=b.userid and a.tdate=b.tdate
order by id

/*--测试结果
id userid tdate type
----------- ----------- ---------------------------- -------
2 1 2003-12-11 10:12:12.000 B
6 2 2003-12-25 10:12:12.000 C
10 3 2003-10-25 19:12:12.000 D

（所影响的行数为 3 行）

--*/

[xiaoyuehen]

多谢楼上的回复...我又测试了一下...

很奇怪, 结果和你们的是一样的(我自己写的那句也没有问题).但在我真实的数据库里查询时却不一样...就是会重复, 差一两分钟的记录会被提取出来...郁闷..

我不知道问题出在哪儿了..

[xiaoyuehen]

完全相同的记录是会被选出来的..如
10,3,'2003-10-25 19:12:12','D' 被追加两次..

[xiaoyuehen]

哈...我明白问题出在哪儿了..

因为Tdate的时间我是记用户输入的, 只有日期, 没有具体到小时分..所以保存时后面全部为00:00:00..这样就出现同样时间记录的可能性就大大增加..导致重复..:(