Cool_Wayen
BigBig_Wayen
采纳率25%
2017-02-14 02:41 阅读 768

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字段,应该这么写啊?想了好久不会写

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • Tiger_Zhao Tiger_Zhao 2017-02-15 01:35
    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
    
    点赞 1 评论 复制链接分享
  • a466631276 C_*_z 2017-02-14 07:46

    有点没懂 你的意思哦!

    点赞 评论 复制链接分享

相关推荐