在经过查询以后的数据中筛选一些想要的数据
如题
我通过多个表 join 得到一些数据，但是在结果中还有很多重复的数据例如：
id department modified
3.1.1.0 abcde 2006-06-10
3.1.1.1 fghij 2006-06-11
3.1.1.2 klmnp 2006-06-12
3.1.1.2 null 2006-06-23
3.1.1.2 fdsfdfs 2006-07-10
。。。。。。
我想得到是
id department modified
3.1.1.0 abcde 2006-06-10
3.1.1.1 fghij 2006-06-11
3.1.1.2 fdsfdfs 2006-07-10
取出id中不重复和时间中显示最大一条记录
[解决办法]
select * from test a where not exists
(
select 1 from test where id=a.id and modified> a.modified
)
[解决办法]
select * from [Table] a where not exists(select 1 from [Table] where id=a.id and modified> a.modified)
[解决办法]
select
t.id,t.department,t.modified
from table t
inner join
(select id,modified = max(modified) from table group by id)
a on a.id = t.id and a.modified = t.modified
哈哈，我测试了一下。估计比not exists慢点。
[解决办法]
Select * From news A
Where (Select Count(*) From news Where id=a.id And modified> a.modified) < 1
Order By id , modified
--方法二：
select * from test a where not exists
(
select 1 from test where id=a.id and modified> a.modified
)
--方法三：
Select * From news A
Where AddDate In (Select TOP 1 modified From news Where id=a.id Order By modified Desc)
Order By id, modified
我也学学鱼兄
