android - 按日期范围和其他条件排序查询时遇到问题。联盟？连接？寻找想法

Question

在我的 Android SQLite 数据库中，我有一列是日期时间，格式为 YYYY-MM-DD HH:MM。我想要一个查询(或实际上是一个游标)，它会给我所有的行，比如说 2015 年 5 月。排序是我卡住的地方。我希望我的结果首先按周分解，然后每周根据其他列进行排序。

现在我只是按日期时间列排序，然后是其他列。例如:ORDER BY t.TASKS_DATETIME ASC, s.STATUSES_RANK ASC, t.TASKS_CATEGORY .但基本上每一行都有一个唯一的 datetime 值(即使在这种情况下我不关心小时和分钟)，所以不会发生其他排序，最终结果不是非常用户友好。排序顺序对于用户查看和理解这些数据非常重要。

我考虑过为每个星期做一个单独的选择语句，然后做一个 UNION ALL获取月份数据。但是你不能使用 ORDER BY带有 UNION 的子句，因为它对整个联合进行排序，而不仅仅是内部的单个 select 语句。更不用说，它非常丑陋。

我想也许我可以在 Android/Java 端做到这一点并连接多个 Cursor 对象。但我找不到任何方法来做到这一点。这里有人知道方法吗？我已经离开 Java 几年了，并且从一开始就对这门语言没有那么熟悉。

我想也许我可以使用 SQLite 日期函数 [ https://www.sqlite.org/lang_datefunc.html ] 某种程度上来说。也许在我的选择中添加一个新列，其中包含一年中的一周。然后我可以按那个新列排序，然后是我想要的其他排序标准。或者也许有一种方法可以将范围(或简化的日期时间)放入 ORDER BY条款。但这正在插入我的 SQLite 技能的界限。这看起来可能吗？仅供引用，在 Android/Java 方面，我将此日期时间字符串转换为 GregorianCalendar。

我希望这里比我聪明的人可以为我指明一个好的方向!这些选项都不是很好，我想不出其他选项。

如果我想不出其他任何东西，我会选择 UNION选项。并在其中的每个选择语句中添加一个“week_of_year”列以标识该周，正如此处其他联合/排序问题所建议的那样。然后按“week_of_year”列排序，然后按我的其他排序标准。我已经开始着手这方面的工作了。但是我休息了一下，在这里寻求更好的想法，因为我的直觉说必须有更好的方法。但也许不是:)

作为额外的背景，我认为这无关紧要，但以防万一......

在 StickyHeaderList [ https://github.com/emilsjolander/StickyListHeaders 中使用了这个可怕的查询。 ]，其中周是标题。如果我不首先按日期时间排序，则标题/周最终会乱序。有时他们甚至会重复。

我可能会在某个时候编写自己的替代方案或更改此 StickyHeaderList 代码，也许是每周/标题需要一个光标而不是整个月的光标。我可以在 Java 中比 SQL 更容易地处理日期。但我今天没有资源。我想，最令人头疼的是让多选 CAB 菜单允许我在不同的列表中进行选择。如果您碰巧知道一个简单的解决方法，也许我会这样做并接受主要的返工，而不是弄清楚这个查询。

更新:它有效!

非常感谢@CL 给了我答案。为了后代和清晰，这里有一些注释。

正确答案是:

SELECT ...
FROM ...
ORDER BY strftime('%Y%W', t.TASKS_DATETIME),
     s.STATUSES_RANK,
     t.TASKS_CATEGORY

如前所述，几周是挑剔的事情。我还需要考虑从一周中的不同日子开始的用户。注意，SQLite 默认是在星期一(不是星期日)开始。另外，关键字是 day不是 days用于计算该偏移量。此外，偏移量应位于日期时间的右侧。

这给我留下了:

 "ORDER BY strftime('%Y %W', t." + TASKS_SCHED_DATETIME + weekOffset + ")," + 
     "s." + STATUSES_RANK + " ASC, " +
     "t." + TASKS_CATEGORY + " DESC "

最后变成: ORDER BY strftime('%Y %W', t.task_sched_datetime, '+5 day' ), s.status_rank ASC, t.tasks_category DESC weekOffset在一个小辅助函数中定义:

private String calculateWeekOfset() {
    int wkStart = App.getWeekStart();
    int offset = 9 - wkStart; // Calendar.MONDAY == 2
    return ", '+" + Integer.toString(offset)+ " day' ";
}

我想就是这样。再次感谢!

Answer 1

要从时间戳中提取字段，请使用 strftime function .
在这种情况下，您需要一年中的一周和一年本身来使不同年份的日期独一无二:

SELECT ...
FROM ...
ORDER BY strftime('%Y%W', t.TASKS_DATETIME),
         s.STATUSES_RANK,
         t.TASKS_CATEGORY

如果您不想从星期一开始一周，请添加或减去适当的天数:

... strftime('%Y%W', t.TASKS_DATETIME, '+1 days'), ...