纠正以下: select * from 表 t where not exists(select 1 from 表 where bianma=t.bianma and convert(datetime,[date])>convert(datetime,t.[date]))
declare @tb table ( bianma int, [date] varchar(10), [shuliang] int ) insert @tb select 1,'2004-12-1',13 union select 1,'2004-9-11',14 union select 3,'2005-4-12',34--测试 select * from @tb t where not exists(select 1 from @tb where bianma=t.bianma and convert(datetime,[date])>convert(datetime,t.[date]))--结果 /* bianma date shuliang ----------- ---------- ----------- 1 2004-12-1 13 3 2005-4-12 34(2 row(s) affected) */
如果bianma+date是候选码的话可以用下面的语句:select a.* from t1 a,(select bianma,max(date) as maxdate from t1 group by bianma) b where a.bianma=b.bianma and a.date=b.maxdate
select distinct(bianma),max(date) from tablename group by bianma
select bianma, date, Max(shuliang) from tablename where date in(select bianma, max(date) as date from tablename as t where t.bianma = tablename.bianma group by bianma) group by bianma, date
select * from 表 t where date = (select Max(date ) from 表 where bianma=t.bianma )
select * from 表 t
where not exists(select 1 from 表 where bianma=t.bianma and convert(datetime,[date])>convert(datetime,t.[date]))
(
bianma int,
[date] varchar(10),
[shuliang] int
)
insert @tb
select 1,'2004-12-1',13 union
select 1,'2004-9-11',14 union
select 3,'2005-4-12',34--测试
select * from @tb t
where not exists(select 1 from @tb where bianma=t.bianma and convert(datetime,[date])>convert(datetime,t.[date]))--结果
/*
bianma date shuliang
----------- ---------- -----------
1 2004-12-1 13
3 2005-4-12 34(2 row(s) affected)
*/
如果bianma+date是候选码的话可以用下面的语句:select a.*
from t1 a,(select bianma,max(date) as maxdate from t1 group by bianma) b
where a.bianma=b.bianma and a.date=b.maxdate
group by bianma
from tablename
where date in(select bianma, max(date) as date
from tablename as t
where t.bianma = tablename.bianma
group by bianma)
group by bianma, date
where date = (select Max(date )
from 表
where bianma=t.bianma )