表中有字段及数据如下:
id date value
1 2009 100
1 2008 101
2 2009 102
3 2006 103
3 2007 104
3 2008 105如何得到如下结果
id date value
1 2009 100
2 2009 102
3 2008 105 实际就是得到id唯一,date最大的一条记录。请高手指教。
id date value
1 2009 100
1 2008 101
2 2009 102
3 2006 103
3 2007 104
3 2008 105如何得到如下结果
id date value
1 2009 100
2 2009 102
3 2008 105 实际就是得到id唯一,date最大的一条记录。请高手指教。
from ta a
where not exists(select 1 from ta where id = a.id and date> a.date)
from 表 t
where not exists(select 1
from 表
where id = a.id
and date> a.date)
select top 1 *from table group by id order by date desc
你试下,desc 和asc 我忘了哪一个是升序,哪一个是降序了你方试用一下
FROM T a
WHERE NOT EXISTS
(
SELECT 1
FROM T b
WHERE
a.id = b.id and
b.[date] > a.[date]
)
(
SELECT id,MAX([date]) AS da FROM table GROUP BY [date]
) B ON (A.id=B.id AND A.[date]=B.da)
insert into @t
select 1 , '2009', 100 union all
select 1 , '2008', 101 union all
select 2 , '2009', 102 union all
select 3 , '2006', 103 union all
select 3 , '2007', 104 union all
select 3 , '2008', 105
select id , value , date from (
select id , value , date , num = (row_number() over (partition by id order by date desc)) from @t
) a where a.num = 1id value date
----------- ----------- ----
1 100 2009
2 102 2009
3 105 2008(3 行受影响)
(
[id] int,
[date] int,
value int
)insert into t2 values(1,2009,100)
insert into t2 values(1,2008,101)
insert into t2 values(2,2009,102)
insert into t2 values(3,2006,103)
insert into t2 values(3,2007,104)
insert into t2 values(3,2008,105)select * from t2方法二:
select * from t2 as t
where date=(
select max(date)
from t2
where [id]=t.[id]
)--方法一:
select t2.[id],t2.[date],value from t2
join
(
select [id],max(date) as d
from t2
group by [id]
) as t1
on t2.[id]=t1.[id] and t2.[date]=t1.d