“简单"SQL查询 [英] "Simple" SQL Query

问题描述

我的每个客户都可以有很多待办事项，而且每个待办事项都有截止日期.

Each of my clients can have many todo items and every todo item has a due date.

在每个文件的截止日期前发现下一个未完成的待办事项的查询是什么?如果客户端有多个待办事项，则 id 最低的那个才是正确的.

What would be the query for discovering the next undone todo item by due date for each file? In the event that a client has more than one todo, the one with the lowest id is the correct one.

假设以下最小架构:

clients (id, name)

todos (id, client_id, description, timestamp_due, timestamp_completed)

谢谢.

推荐答案

我还没有测试过这个，所以你可能需要调整它:

I haven't tested this yet, so you may have to tweak it:

SELECT
    TD1.client_id,
    TD1.id,
    TD1.description,
    TD1.timestamp_due
FROM
    Todos TD1
LEFT OUTER JOIN Todos TD2 ON
    TD2.client_id = TD1.client_id AND
    TD2.timestamp_completed IS NULL AND
    (
        TD2.timestamp_due < TD1.timestamp_due OR
        (TD2.timestamp_due = TD1.timestamp_due AND TD2.id < TD1.id)
    )
WHERE
    TD2.id IS NULL

您没有尝试排序和汇总，而是回答了这个问题，在这个问题之前还有其他待办事项吗?"(基于您对之前"的定义).如果没有，那么这就是您想要的.

Instead of trying to sort and aggregate, you're basically answering the question, "Is there any other todo that would come before this one?" (based on your definition of "before"). If not, then this is the one that you want.

这应该在大多数 SQL 平台上都有效.

This should be valid on most SQL platforms.

这篇关于“简单"SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！