my_tbl:ID date value
0 2008-1-1 3
1 2008-1-2 5
2 2008-1-1 1
3 2008-1-2 4
4 2008-1-2 2需要这样,
1 取ID>0的子集,以下操作对这个子集进行
2 日期相同的为一组
3 组内记录按ID降序排列
4 得到每组的第一条记录
0 2008-1-1 3
1 2008-1-2 5
2 2008-1-1 1
3 2008-1-2 4
4 2008-1-2 2需要这样,
1 取ID>0的子集,以下操作对这个子集进行
2 日期相同的为一组
3 组内记录按ID降序排列
4 得到每组的第一条记录
select * from my_tbl a where not exists(select 1 from my_tbl where date=a.date and id>a.id)
--少了个条件:
select * from my_tbl a where not exists(select 1 from my_tbl where date=a.date and id>a.id)
and id>0
declare @my_tb1 table(id int,date smalldatetime,value int)
insert @my_tb1
select 0, '2008-1-1', 3 union all
select 1, '2008-1-2', 5 union all
select 2, '2008-1-1', 1 union all
select 3, '2008-1-2', 4 union all
select 4, '2008-1-2', 2 select a.* from @my_tb1 as a
join (select date,min(id) as id from @my_tb1 where id>0 group by date) b
on a.id=b.id and a.date=b.date
order by a.id
insert @my_tb1
select 0, '2008-1-1', 3 union all
select 1, '2008-1-2', 5 union all
select 2, '2008-1-1', 1 union all
select 3, '2008-1-2', 4 union all
select 4, '2008-1-2', 2 select a.* from @my_tb1 as a
join (select date,max(id) as id from @my_tb1 where id>0 group by date) b
on a.id=b.id and a.date=b.date
order by a.id
试试看有没有问题
select * from @my_tb1 a where id>0 and id in(select max(id) from @my_tb1 group by date)
--借用四楼的数据
declare @my_tb1 table(id int,date smalldatetime,value int)
insert @my_tb1
select 0, '2008-1-1', 3 union all
select 1, '2008-1-2', 5 union all
select 2, '2008-1-1', 1 union all
select 3, '2008-1-2', 4 union all
select 4, '2008-1-2', 2 select * from @my_tb1 a where not exists (select 1 from @my_tb1 where a.date = date and a.id<id)
and id>0