sqlserver SQL语句

图片说明
这是数据,sql语句是 select targstartdate,wonum,DATEDIFF(mm,targstartdate,GETDATE()) as a from wo where worktype = 'adt' and status in ('comp','close') order by wo.targstartdate desc
现在我想查找出每种a大于0的时间最大的那一条, 结果应该是第5条第9 条 第28 条,
而且只想要wonum字段,应该这么写啊?想了好久不会写

sql

2个回答

有点没懂 你的意思哦!

WITH x AS (
    SELECT targstartdate,
           wonum,
           DATEDIFF(mm,targstartdate,GETDATE()) as a
      FROM wo
     WHERE worktype = 'adt'
       AND status IN ('comp','close')
)
        SELECT y.a,
               z.wonum
          FROM (
                    SELECT a
                      FROM x
                     WHERE a > 0
                  GROUP BY a
               ) y
   CROSS APPLY (
                    SELECT TOP 1 wonum
                      FROM x
                     WHERE x.a = y.a
                  ORDER BY targstartdate DESC
               ) z
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问