如题
我通过多个表 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-113.1.1.2 fdsfdfs 2006-07-10
取出id中不重复和时间中显示最大一条记录
我通过多个表 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-113.1.1.2 fdsfdfs 2006-07-10
取出id中不重复和时间中显示最大一条记录
解决方案 »
- 谁有SQL Server 2000的ODBC驱动jar包?跪求!
- 一条麻烦的SQL语句
- 一个会员表,5万条记录,SQL语句读取的时候超时了,求解决办法!
- 求一SQL语句????~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- 数据库日期问题
- SQL 表之间关联 递减
- 触发器的使用方法
- 怎样将RECORDSET里面的数据直接INSERT进表里面去,不考虑将RECORDSET的数据取出来再处理!
- 有没有办法加密储存过程
- select中查询速度很快,但在fetch速度却不行?怎么办????
- 怎样知道一条数据在不同排序方法下的排名?
- 帮忙看看一小问题,分太少,对不住大家了
(
select 1 from test where id=a.id and modified>a.modified
)
insert test select '3.1.1.0','abcde','2006-06-10'
union all select '3.1.1.1','fghij','2006-06-11'
union all select '3.1.1.2','klmnp','2006-06-12'
union all select '3.1.1.2',null,'2006-06-23'
union all select '3.1.1.2','fdsfdfs','2006-07-10'select * from test a where not exists
(
select 1 from test where id=a.id and modified>a.modified
)id department modified
---------- ---------- ------------------------
3.1.1.0 abcde 2006-06-10 00:00:00.000
3.1.1.1 fghij 2006-06-11 00:00:00.000
3.1.1.2 fdsfdfs 2006-07-10 00:00:00.000
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慢点。
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 我也学学鱼兄
from #temp a,
(
select ID,max(modified) maxDate
from #temp
group by ID
) b
where a.ID=b.ID
and a.modified=b.maxDate