SQL:如何根据日期(包括空大小写)获取唯一记录

Question

假设我们有这些表，引用要为单个客户执行的任务

AGENDAS
ID | DAY
86 | 2017-04-14
88 | 2017-04-22

WORKS
ID                   | IDAGENDA | TIME SLOT |  RESULT  | DATETIMEEXECUTED | IDORDER
IZ9`fiq|-=rRE7OKKO;P |    86    |  8-10 am  | canceled | 2017-04-13 10:45 | 10
g5wMkHa4Z?bQnFqDmXKn |    88    |  8-10 am  | canceled | 2017-04-19 16:30 | 10
n\mkLegfO>HSCJ/X:{ax |    88    |  13-15 pm |          |                  | 10

使用我(被迫)使用的框架，当我创建一个面板时，我可以编写一个查询，以便获取它的数据。这个查询称为主查询。我的老板想要一个简要的屏幕，在那里，每一个工作顺序，我必须列出最近采取的议程，以防万一，任务的结果。那么，让我们假设我们有上述表格的情况。在这种情况下，我应该只列出ID n\mkLegfO>HSCJ/X:{ax的任务，因为它是最近插入和保留的任务，还有待执行(客户没有取消它)。但是我不知道如何获取这个唯一的记录，因为如果在主查询中，我写：

select ...
from ORDERS O left join AGENDAS A on ...
     left join WORKS W1 on ...
where W1.IDORDER = 10 and W1.DATETIMEEXECUTED = (
   select max(W2.DATETIMEEXECUTED)
   from WORKS W2
   where W2.IDORDER = 10
)

然后记录

g5wMkHa4Z?bQnFqDmXKn |    88    |  8-10 am  | canceled | 2017-04-19 16:30

会被退回，这不是最近的。另一方面，如果我尝试：

select ...
from ORDERS O left join AGENDAS A on ...
     left join WORKS W1 on ...
where W1.IDORDER = 10 and (DATETIMEEXECUTED is null or  W1.DATETIMEEXECUTED = (
   select max(W2.DATETIMEEXECUTED)
   from WORKS W2
   where W2.IDORDER = 10)

然后我得到：

 g5wMkHa4Z?bQnFqDmXKn |    88    |  8-10 am  | canceled | 2017-04-19 16:30 | 10
 n\mkLegfO>HSCJ/X:{ax |    88    |  13-15 pm |          |                  | 10

这还是不对的。我认为最大的问题是我不能在max(ID)表上执行WORKS聚合函数，因为这些记录是由现场工作人员在移动设备中创建的，PK不能只是一个整数计数器，每个设备移动为SQLite，每个操作员在与中央服务器同步之前创建自己的记录。这就是为什么我们的框架提供一个PK作为char(20)伪随机字符。

因为我是个白痴，我不认为创建工作的日期时间，但是这个应用程序已经在生产中了，所以我不想更改DB模式。此外，left join之所以需要，是因为对于另一种情况，仍然不存在任何议程和任命。

因此，在最后:如果只有运河的作品，我想看看最近的一件。相反，如果还有一项工作要做(结果和日期执行时间等于null)，我希望得到它。有解决办法吗？

Answer 1

idorder =10的最新记录：

select top(1) *
from works
where idorder = 10 
  and coalesce(result, 'okay') <> 'canceled'
order by datetimeexecuted desc;

每件订单的最新记录：

select *
from
(
  select *, row_number() over (partition by idorder order by datetimeexecuted desc) as rn
  from works
  where coalesce(result, 'okay') <> 'canceled'
) ranked
where rn = 1;

更新:如果每个idorder都有最新未取消的记录，则为，否则为最新的其他记录：

select *
from
(
  select *, 
    row_number() over (partition by idorder order by 
      case when result = 'canceled' then 2 else 1 end, datetimeexecuted desc) as rn
  from works
) ranked
where rn = 1;

Answer 2

更新

正如Thorsten Kettner在他的“好答案”中所建议的那样，我创建了一个新的标准视图，其查询恰好是内部SELECT *, row_number()...，我在面板的主查询中使用了该视图，而不是WORKS的经典表。最后，为了更准确地说明我的情况，我必须执行以下操作：

select ...
from ORDERS O left join AGENDAS A on ...
              left join VIEW V on ...
where V.rn = 1 or (V.rn is null)

因为如果工作/约会仍然没有被插入，他们无论如何都希望看到其他相关的数据。