try: select CellMaster.[LineNo], CellMaster.CellNo, (select top 1 StatusDate from CellMaster where CellMaster.CellNo = CellStatus.CellNo AND CellMaster.[LineNo] = CellStatus.[LineNo] order by StatusDate DESC) as StatusDate, (select top 1 Status from CellMaster where CellMaster.CellNo = CellStatus.CellNo AND CellMaster.[LineNo] = CellStatus.[LineNo] order by StatusDate DESC) as Status from CellMaster
没有直接对应的功能,可以借助临时表实现:select identity(int,1,1) as id,* into #CellStatus from CellStatusselect a.LineNo,a.CellNo,b.StatusDate as [Date],b.Status from CellMaster a left join #CellStatus b on a.CellNo=b.CellNo and a.LineNo=b.LineNo and not exists(select 1 from #CellStatus where CellNo=b.CellNo and LineNo=b.LineNo and id>b.LineNo)
select CellMaster.[LineNo], CellMaster.CellNo,
(select top 1 StatusDate from CellMaster where CellMaster.CellNo = CellStatus.CellNo AND CellMaster.[LineNo] = CellStatus.[LineNo] order by StatusDate DESC) as StatusDate,
(select top 1 Status from CellMaster where CellMaster.CellNo = CellStatus.CellNo AND CellMaster.[LineNo] = CellStatus.[LineNo] order by StatusDate DESC) as Status
from CellMaster
a.LineNo,a.CellNo,b.StatusDate as [Date],b.Status
from
CellMaster a
left join
#CellStatus b
on
a.CellNo=b.CellNo and a.LineNo=b.LineNo
and
not exists(select 1 from #CellStatus where CellNo=b.CellNo and LineNo=b.LineNo and id>b.LineNo)