如何在sqlite中获得一组结果日期

Question

我正在做android应用程序，我必须找到结果日期组。
我遵循日期在sqlite数据库

2013-05-04
2013-05-06
2013-05-07
2013-05-08
2013-05-10
2013-05-11
2013-05-12
2013-05-13
2013-05-14
2013-05-15
2013-05-16
2013-05-17
2013-05-26
2013-05-27

我想按照以下方式对它们进行分组

2013-05-04

2013-05-06
2013-05-07
2013-05-08

2013-05-10
2013-05-11
2013-05-12
2013-05-13
2013-05-14
2013-05-15
2013-05-16
2013-05-17

2013-05-26
2013-05-27

Answer 1

SQL组不是你认为的那个组

您可以对子字符串进行排序:

sqlite> select d from t order by substr(d, 0, 10);
2013-05-04
2013-05-06
2013-05-07
2013-05-08
2013-05-10
2013-05-11
2013-05-12
2013-05-13
2013-05-14
2013-05-15
2013-05-16
2013-05-17
2013-05-26
2013-05-27

Answer 2

如果记录是组中的第一条记录，则将第二列的值设置为1。("第一个"记录是指没有前一个日期记录的任何记录。)

SELECT MyDate,
       NOT EXISTS (SELECT 1
                   FROM MyTable AS T2
                   WHERE T2.MyDate = date(T1.MyDate, '-1 days')
                  ) AS FirstInGroup
FROM MyTable AS T1
ORDER BY MyDate

示例输出:

2013-05-04  1
2013-05-06  1
2013-05-07  0
2013-05-08  0
2013-05-10  1
2013-05-11  0
2013-05-12  0
2013-05-13  0
2013-05-14  0
2013-05-15  0
2013-05-16  0
2013-05-17  0
2013-05-26  1
2013-05-27  0