select a.*
from aTable a
inner join (select id,c_no,c_dt=max(c_dt) from aTable group by id,c_no)b
on a.id=b.id and a.c_no=b.c_no and a.c_dt=b.c_dt
from aTable a
inner join (select id,c_no,c_dt=max(c_dt) from aTable group by id,c_no)b
on a.id=b.id and a.c_no=b.c_no and a.c_dt=b.c_dt
from aTable a
inner join (select c_no,c_dt=max(c_dt) from aTable group by c_no)b
on a.c_no=b.c_no and a.c_dt=b.c_dt
where a.c_no=t.c_no and a.c_dt=t.c_dt
ID C_NO C_DT C_ADDR
1 2 2005-01-01 12:00:00 A
2 3 2005-01-01 12:30:00 A
3 3 2004-01-01 11:23:00 B
4 2 2005-04-15 02:12:00 V
5 1 2003-02-05 03:24:00 C那么结果集可能:
2 3 2005-01-01 12:30:00 A
3 3 2004-01-01 11:23:00 B
...
以上2笔的号码重复,但是他们的确就是改天的最新时间的纪录,wangang2436 () 同志,请问你的结果可能做到吗?最大又要c_no不重复,看你的举例,10有8,9有可能号码重复Aah!!!
select a.* from aTable a
where (a.C_DT) =
(select max(C_DT) from aTable where ID= a.IDorder by C_DT )
--测试环境
declare @t table (ID int identity(1,1),C_NO int ,C_DT datetime,C_ADDR varchar(4))
insert into @t select 2,'2005-01-01 12:00:00','A'
union all select 3,'2003-11-01 12:00:00','A'
union all select 3,'2004-01-01 11:23:00','B'
union all select 2,'2005-04-15 02:12:00','V'
union all select 1,'2003-02-05 03:24:00','C'
--查询
select a.* from @t a
where (a.C_DT) =
(select top 1 C_DT from @t where C_NO= a.C_NO order by C_DT desc )
--结果
ID C_NO C_DT C_ADDR
----------- ----------- ----------------- ------
3 3 2004-01-01 11:23:00.000 B
4 2 2005-04-15 02:12:00.000 V
5 1 2003-02-05 03:24:00.000 C(所影响的行数为 3 行)
我的很简单吧。
where a.c_no=t.c_no and a.c_dt=t.c_dt
和 select max(C_DT),C_NO from aTable group by C_NO
两者的数据量不一致
和select a.* from aTable a,(select max(C_DT),C_NO from aTable group by C_NO)t
where a.c_no=t.c_no and a.c_dt=t.c_dt
两者查询的数据结果,行数不一致, 哪一定有问题
INSERT atable SELECT 2, '2005-01-01 12:00:00', 'A'
UNION ALL
SELECT 3, '2003-11-01 12:00:00', 'A'
UNION ALL
SELECT 3, '2004-01-01 11:23:00', 'B'
UNION ALL
SELECT 2, '2005-04-15 02:12:00', 'V'
UNION ALL
SELECT 1, '2003-02-05 03:24:00', 'C'对不起笔误是这样的select * from atable where id in (select max(id) from atable group by c_no)
所影响的3行如下:3 2004-01-01 11:23:00.000 B 4
2 2005-04-15 02:12:00.000 V 5
1 2003-02-05 03:24:00.000 C
from aTable
where c_no||aTable.c_dt in
(select c_no||max(c_dt) from aTable group by c_no)
order by id
3 3 2004-01-01 11:23:00.000 B
4 2 2005-04-15 02:12:00.000 V
5 1 2003-02-05 03:24:00.000 C
INSERT atable SELECT 2, '2005-01-01 12:00:00', 'A'
UNION ALL
SELECT 3, '2003-11-01 12:00:00', 'A'
UNION ALL
SELECT 3, '2004-01-01 11:23:00', 'B'
UNION ALL
SELECT 2, '2005-04-15 02:12:00', 'V'
UNION ALL
SELECT 1, '2003-02-05 03:24:00', 'C'select * from atable where id in (select max(id) from atable group by c_no)
所影响的3行如下: id C_NO C_DT C_ADDR
3 3 2004-01-01 11:23:00.000 B
4 2 2005-04-15 02:12:00.000 V
5 1 2003-02-05 03:24:00.000 C
4 2 2005-04-15 02:12:00 V
那条啊,到底是不是取最新日期啊?
是取同一型号最大ID而不是最新C_DT吧
a.*
from
aTable a
where
ID = (select top 1 ID from aTable where C_NO = a.C_NO order by C_DT desc)
select
a.*
from
aTable a
where
not exists (select 1 from aTable where C_NO = a.C_NO and C_DT > a.C_DT)
select
a.*
from
aTable a
where
a.C_DT = (select max(C_DT) from aTable where C_NO = a.C_NO)....