sql 查询语句(100分)

有2个ms-sqlserver的table，一个Device如下<br>ID &nbsp; &nbsp;Name &nbsp; &nbsp;Model<br>1 &nbsp; &nbsp; aa &nbsp; &nbsp; &nbsp;dsd<br>2 &nbsp; &nbsp; df &nbsp; &nbsp; &nbsp;sd<br>3 &nbsp; &nbsp; ds &nbsp; &nbsp; &nbsp;dss<br>4 &nbsp; &nbsp; dds &nbsp; &nbsp; &nbsp;er<br>另一个table 名ServRec如下<br>ID DeviceID &nbsp;ServDate SupplierID<br>1 &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp;2007/1/1 &nbsp; &nbsp; 2<br>2 &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp;2006/1/2 &nbsp; &nbsp; 3<br>3 &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp;2005/2/3 &nbsp; &nbsp; 1<br>4 &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp;2007/1/3 &nbsp; &nbsp; 4<br>5 &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp;2008/1/4 &nbsp; &nbsp; 1<br>希望查询后出现<br>DeviceID &nbsp; &nbsp;Name &nbsp; &nbsp;Model ServDate &nbsp; &nbsp;SupplierID<br>&nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; aa &nbsp; &nbsp; &nbsp;dsd &nbsp; &nbsp; 2007/1/1 &nbsp; &nbsp; 2<br>&nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; df &nbsp; &nbsp; &nbsp;sd &nbsp; &nbsp; &nbsp;2008/1/4 &nbsp; &nbsp; 1<br>&nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; ds &nbsp; &nbsp; &nbsp;dss &nbsp; &nbsp; &nbsp;null &nbsp; &nbsp; &nbsp; null<br>&nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; dds &nbsp; &nbsp; &nbsp;er &nbsp; &nbsp; &nbsp;null &nbsp; &nbsp; &nbsp; null<br><br>我想选择Device表中所有记录，然后左连接ServRec中ServDate为最新的那条记录<br>SELECT MAX(ServRec.ServDate) AS ServDate, ServRec.SupplierID, Device.Name, <br>&nbsp; &nbsp; &nbsp; Device.Model，Device.ID AS DeviceID<br>FROM Device LEFT OUTER JOIN<br>&nbsp; &nbsp; &nbsp; ServRec ON Device.ID = ServRec.DeviceID<br>GROUP BY ServRec.SupplierID, Device.Name, Device.Model，Device.ID<br>出现结果<br>DeviceID &nbsp; &nbsp;Name &nbsp; &nbsp;Model ServDate &nbsp; &nbsp;SupplierID<br>&nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; aa &nbsp; &nbsp; &nbsp;dsd &nbsp; &nbsp; 2007/1/1 &nbsp; &nbsp; 2<br>&nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; aa &nbsp; &nbsp; &nbsp;dsd &nbsp; &nbsp; 2006/1/2 &nbsp; &nbsp; 3<br>&nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; df &nbsp; &nbsp; &nbsp;sd &nbsp; &nbsp; &nbsp;2007/1/3 &nbsp; &nbsp; 4<br>&nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; df &nbsp; &nbsp; &nbsp;sd &nbsp; &nbsp; &nbsp;2008/1/4 &nbsp; &nbsp; 1<br>&nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; ds &nbsp; &nbsp; &nbsp;dss &nbsp; &nbsp; &nbsp;null &nbsp; &nbsp; &nbsp; null<br>&nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; dds &nbsp; &nbsp; &nbsp;er &nbsp; &nbsp; &nbsp;null &nbsp; &nbsp; &nbsp; null<br>怎样才能得到我要的结果？

这样可以，不过效率不好<br><br>select c.id as deviceid,c.name,c.model,d.servdate,d.supplierid<br>from device c left join<br>(<br>&nbsp; select b.deviceid,b.ServDate,b.supplierid from<br>&nbsp; &nbsp; (<br>&nbsp; &nbsp; &nbsp; select deviceid,max(servdate) as servdate from servrec group by deviceid<br>&nbsp; &nbsp; ) a <br>&nbsp; &nbsp;left join<br>&nbsp; &nbsp; (<br>&nbsp; &nbsp; &nbsp; select ID,DeviceID,ServDate,SupplierID from servrec<br>&nbsp; &nbsp; &nbsp b <br>&nbsp; &nbsp;on b.DeviceID=a.deviceid and b.ServDate=a.ServDate<br>) d<br>&nbsp;on c.id=d.DeviceID

先在ServRec表中找到每一个DeviceID的最新记录,然后右外连接Device表.这样有最新记录的会出现,没有最新记录的Name 也会出现

顶一下,没有测试哦,我哥们电脑上面玩的.<br>select max(t2.servDate)as ServDate,isnull(t2.supplierID,0) as SupplierID,<br>t1.ID,t1.name,t1.Model<br>from servRec t2 right outer join Device t1 on t2.DeviceID=t1.Device<br>Group by t2.SupplierID,t1.ID,t1.Name,t1.Model

007vivi,zhaojj,结果都不对啊。

也可以把要显示最新的记录放到主查询的SELECT语句中，子查询中取得日期为最新的那条数据，只是这样速度可能上不去，仅提供方法供参考。

如：select DeviceID,name,(select ServDate ,.... from ServRec where () &nbsp;order by ServDate &nbsp;desc)

select DeviceID,Name,Model,ServDate,SupplierID from Device as e left join (<br> select b.* from (<br> select deviceid,max(servdate) as servdate from servrec group by deviceid<br> ) as a join servrec as b on a.deviceid=b.deviceid and a.servdate=b.servdate<br>) as f on a.id=b.id

测试通过 楼主可以试试<br>Select A.ID as DeviceID,A.Name,A.Model,B.ServDate,B.SupplierID<br>From Device A Left Outer join <br>(<br>&nbsp;Select DeviceID,ServDate,SupplierID From ServRec Where ServDate in<br>&nbsp;( Select Max(ServDate) From ServRec Group by DeviceID <br>&nbsp<br>) B <br>on A.ID = B.DeviceID

select c.id as deviceid,c.name,c.model,b.servdate,b.supplierid<br>from device c left join<br>&nbsp;(select deviceid,max(servdate) as servdate from servrec group by deviceid ) a <br>&nbsp;left join &nbsp;servRec b &nbsp; on b.DeviceID=a.deviceid and b.ServDate=a.ServDate<br>&nbsp;on c.id=b.DeviceID

多人接受答案了。