在SQLite中，ORDER BY Month，Year DESC无法正常工作。

Question

我有一个带日期字段的表格，我正在将其转换为月份、年份和日期（3个单独的列）。它运行良好，但是我需要按照月份和年份的降序对结果进行排序。

这是我的查询：

Cursor c = database.rawQuery(
    "select count(*) as TotalCount, tDate, " +
        "sum(case when type = '1' then Amount else 0 end) \"Income\", " +
        "sum(case when type = '2' then Amount else 0 end) \"Expenses\", " +
        "(sum(case when type = '1' then Amount else 0 end) - sum(case when type = '2' then Amount else 0 end)) \"Profit\", " +
        "strftime('%d', tDate) as DayPart, " +
        "strftime('%m', tDate) as MonthPart, " +
        "strftime('%Y', tDate) as YearPart " +
        "from library " +
        "group by MonthPart, YearPart Order by YearPart,MonthPart DESC", 
    null
);

它是以降序显示的，但仅显示MonthPart而不是YearPart和MonthPart。请帮忙。

- Mahesh

---

不要存储日期的部分，将其作为一列存储。 - Jens

Answer 1

我已经找到了答案: -

Cursor c = database.rawQuery(
    "select count(*) as TotalCount, tDate, " +
            "sum(case when type = '1' then Amount else 0 end) \"Income\", " +
            "sum(case when type = '2' then Amount else 0 end) \"Expenses\", " +
            "(sum(case when type = '1' then Amount else 0 end) - sum(case when type = '2' then Amount else 0 end)) \"Profit\", " +
            "strftime('%d', tDate) as DayPart, " +
            "strftime('%m', tDate) as MonthPart, " +
            "strftime('%Y', tDate) as YearPart " +
            "from library " +
            "group by MonthPart, YearPart ORDER BY date(tDate) DESC", 
    null
);

- Mahesh