求教各位一个问题很紧急...拜托.
有一张视图ViewWeavingplan:字段如下:id,quweihao,jiqihao,productId,empId,banci,planDate(计划时间),zhongliang,michang,xiafu,name,productName,productSpecs,machineName,locationName,Rutime(录入计划的时间)
1.现在要根据jiqihao分组,一个机器号只能出现一次,而且要根据rutime来确定最近的一次(可能有两条相同的记录,就是rutime不一样,要最近的时间的那个)
有一张视图ViewWeavingplan:字段如下:id,quweihao,jiqihao,productId,empId,banci,planDate(计划时间),zhongliang,michang,xiafu,name,productName,productSpecs,machineName,locationName,Rutime(录入计划的时间)
1.现在要根据jiqihao分组,一个机器号只能出现一次,而且要根据rutime来确定最近的一次(可能有两条相同的记录,就是rutime不一样,要最近的时间的那个)
select *
from ViewWeavingplan t
where not exists (select 1 from ViewWeavingplan where jiqihao = t.jiqihao and rutime > t.rutime)
select *
from ViewWeavingplan t
where rutime = (select max(rutime) from ViewWeavingplan where jiqihao = t.jiqihao)
where rn = 1
AcHerat的方法好像可以,我再看看..
--再试试select *
from ViewWeavingplan t
where id = (select top 1 id from ViewWeavingplan where jiqihao = t.jiqihao order by Rutime desc)
from ViewWeavingplan t
where rutime = (select max(rutime) from ViewWeavingplan where jiqihao = t.jiqihao and 机器号字段=t.机器号字段)