SQL语句的难题,请路过的进来看看!!(100分)

table1;<br>deviceid &nbsp;name &nbsp; &nbsp; &nbsp; model &nbsp; &nbsp; &nbsp; servdate &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;supplierid<br>1 aa &nbsp; &nbsp; &nbsp; &nbsp; dsd &nbsp; &nbsp; &nbsp; 2006-01-02 00:00:00.000 3 &nbsp; &nbsp; &nbsp; &nbsp; <br>1 aa &nbsp; &nbsp; &nbsp; &nbsp; dsd &nbsp; &nbsp; &nbsp; 2007-01-01 00:00:00.000 2 [] &nbsp;//日期最新的 &nbsp; &nbsp; &nbsp;<br>2 df &nbsp; &nbsp; &nbsp; &nbsp; sd &nbsp; &nbsp; &nbsp; &nbsp; 2005-02-03 00:00:00.000 1 &nbsp; &nbsp; &nbsp; &nbsp; <br>2 df &nbsp; &nbsp; &nbsp; &nbsp; sd &nbsp; &nbsp; &nbsp; &nbsp; 2007-01-03 00:00:00.000 4 &nbsp; &nbsp; &nbsp; &nbsp; <br>2 df &nbsp; &nbsp; &nbsp; &nbsp; sd &nbsp; &nbsp; &nbsp; &nbsp; 2008-01-04 00:00:00.000 1 [] &nbsp; &nbsp; &nbsp; &nbsp;<br>3 ds &nbsp; &nbsp; &nbsp; &nbsp; dss &nbsp; &nbsp; &nbsp; NULL NULL<br>4 dds &nbsp; &nbsp; &nbsp; er &nbsp; &nbsp; &nbsp; &nbsp; NULL NULL<br>想实现如下这样的表,请问SQL怎么写呢?谢谢先<br>deviceid &nbsp;name &nbsp; &nbsp; &nbsp; model &nbsp; &nbsp; &nbsp; servdate &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;supplierid<br>1 aa &nbsp; &nbsp; &nbsp; &nbsp; dsd &nbsp; &nbsp; &nbsp; 2007-01-01 00:00:00.000 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>2 df &nbsp; &nbsp; &nbsp; &nbsp; sd &nbsp; &nbsp; &nbsp; &nbsp; 2008-01-04 00:00:00.000 1 &nbsp; &nbsp; &nbsp; &nbsp; <br>3 ds &nbsp; &nbsp; &nbsp; &nbsp; dss &nbsp; &nbsp; &nbsp; NULL NULL<br>4 dds &nbsp; &nbsp; &nbsp; er &nbsp; &nbsp; &nbsp; &nbsp; NULL NULL

select deviceid, &nbsp;name, &nbsp; &nbsp; &nbsp; model, &nbsp; &nbsp; &nbsp;max(servdate), &nbsp; &nbsp; &nbsp;supplierid<br>from table<br>group by deviceid name model supplierid

这样做偶想到了，可是得不到我要的效果哦,仍然谢谢你!!

select * from tab a,(<br>&nbsp; select deviceid, max(servdate) as servdate from tab group by deviceid<br>) b<br>where a.deviceid=b.deviceid and a.servdate = b.servdate

你的答案有点接近了,答案如下<br>2 df &nbsp; &nbsp; &nbsp; &nbsp; sd &nbsp; &nbsp; &nbsp; &nbsp; 2008-01-04 00:00:00.000 1 &nbsp; &nbsp; &nbsp; &nbsp; 2 2008-01-04 00:00:00.000<br>1 aa &nbsp; &nbsp; &nbsp; &nbsp; dsd &nbsp; &nbsp; &nbsp; 2007-01-01 00:00:00.000 2 &nbsp; &nbsp; &nbsp; &nbsp; 1 2007-01-01 00:00:00.000

没注意还有NULL值<br>select * from tab a,(<br>&nbsp; select deviceid, max(servdate) as servdate from tab group by deviceid<br>) b<br>where a.deviceid=b.deviceid and isnull(a.servdate,0) = isnull(b.servdate,0)

你的结果后面多了两列的

多了deviceid,ervdate两列的<br>请教下，你这种方法的用的交叉连接吗??

改成select a.*就不会有b表的内容了<br>while相当于INNER JOIN吧

娃哈哈!谢谢拉!加分咯!!周末愉快!!