时间戳

首页 > 解决方案 > 选择具有特定条件的最新行

sql - 选择具有特定条件的最新行

问题描述

我有像这样的表1的任务进度表1。任务

ID | TaskID | IsPlanned | MondayOfWeekDate| Progress | UserID | TaskCreationDate
--------------------------------------------------------------------------------------------
18998 9917       1           2019-04-01    0           1150        2019-04-04 10:47:52.177

我需要选择进度<100且TaskCreationDate是最新的任务进度

我试过使用 group by 和 max 但我仍然得到重复的结果

到目前为止，这是我的 SQL

select TaskProgress.* 
from 
    (select 
         TaskID, Max(Progress) as Progress, Max(TaskCreationDate) as TaskCreationDate 
     from 
         TaskProgress
     group by 
         TaskID) as MaxValueTable 
join 
    TaskProgress on TaskProgress.TaskID = MaxValueTable.TaskID
                 and MaxValueTable.Progress = TaskProgress.Progress
                 and TaskProgress.Progress < 100

我希望获得最新创建日期和最高进度点的任务

ID    | TaskID | IsPlanned | MondayOfWeekDate| Progress | UserID | TaskCreationDate 
--------------------------------------------------------------------------------------------
28885   19500       0           2019-03-25       90        1717   2019-03-25 07:57:36.560

但我仍然得到这个

ID    | TaskID | IsPlanned | MondayOfWeekDate| Progress | UserID | TaskCreationDate 
--------------------------------------------------------------------------------------------
28885   19500       0           2019-03-25       90        1717   2019-03-25 07:57:36.560
28696   19500       0           2019-03-11       90        1717   2019-03-16 20:57:57.373
28569   19500       0           2019-03-04       90        1717   2019-03-09 11:29:49.010
28485   19500       0           2019-02-25       90        1717   2019-03-02 18:12:25.587
28368   19500       0           2019-02-18       90        1717   2019-02-23 17:59:21.973

请帮我改正。先感谢您。

标签： sqlsql-server

解决方案

您错过了 MaxValueTable.TaskCreationDate = TaskProgress.TaskCreationDate 的条件

select TaskProgress.* 
from  (
  select 
         TaskID
         , Max(Progress) as Progress
         , Max(TaskCreationDate) as TaskCreationDate 
  from TaskProgress
group by TaskID) as MaxValueTable 
join  TaskProgress on TaskProgress.TaskID = MaxValueTable.TaskID
                 and MaxValueTable.Progress = TaskProgress.Progress
                 and TaskProgress.Progress < 100
                 and  MaxValueTable.TaskCreationDate = TaskProgress.TaskCreationDate

推荐阅读