各位大侠们:
我有一个表m
id aaid ny
1 1 200605
2 1 200708
3 2 200504
4 2 200708
5 2 200908
我的ny字段的类型是char类型我想取出aaid相同的只要ny最大的一条记录
我想要的结果是
id aaid ny
1 1 200708
5 2 200908我认为ny的char类型转换成DATETIME类型就行了 但是我这么转换的convert(datetime,ny,102)报错
我有一个表m
id aaid ny
1 1 200605
2 1 200708
3 2 200504
4 2 200708
5 2 200908
我的ny字段的类型是char类型我想取出aaid相同的只要ny最大的一条记录
我想要的结果是
id aaid ny
1 1 200708
5 2 200908我认为ny的char类型转换成DATETIME类型就行了 但是我这么转换的convert(datetime,ny,102)报错
*
from
tb t
where
id=(select max(id) from tb where aaid=t.aaid)
id aaid ny
1 1 200605
2 1 200708
3 2 200504
4 2 200908
5 2 200708
select t.* from tb t where not exists (select 1 from tb where aaid = t.aaid and ny > t.ny)
insert into tb values(1 , 1 , '200605')
insert into tb values(2 , 1 , '200708')
insert into tb values(3 , 2 , '200504')
insert into tb values(4 , 2 , '200708')
insert into tb values(5 , 2 , '200908')
goselect t.* from tb t where ny = (select max(ny) from tb where aaid = t.aaid) order by t.aaid
select t.* from tb t where not exists (select 1 from tb where aaid = t.aaid and ny > t.ny) order by t.aaiddrop table tb /*
id aaid ny
----------- ----------- ----------
2 1 200708
5 2 200908(所影响的行数为 2 行)id aaid ny
----------- ----------- ----------
2 1 200708
5 2 200908(所影响的行数为 2 行)*/
随便什么样子的都行 因为都是ASCII码
id aaid ny
1 1 200605
2 1 200708
3 2 200504
4 2 200908
5 2 200708
6 2 200708
为什么我这样出来
select max(ny) as num from m where aaid=2 group by daid 的只有1条记录呢 我认为应该是两条