如何在Android rawQuery的SQLite中获取最接近当前时间的行#

问题描述

专家，我想从 MyColumn 列中获取值，其中 Judge 列为真，而 DateTime 列最接近当前时间.如何处理这个问题?谢谢.

Experts,I would like to get the value from column MyColumn, where column Judge is true, and column DateTime is closest to current time. How to approach this? Thanks.

我开发如下代码似乎可行.但是，当表仅包含行 Judge='false' 时(由于我的 where 条件之一是 Judge='true'，期望得到 0 行)我得到:

I develop code like below seems work. However, when the table contains rows only Judge='false', (expect to get 0 rows since one of my where condition is Judge='true') I got:

cursor.moveToFirst()==true

cursor.moveToFirst()==true

cursor.getCount()==1

cursor.getCount()==1

因此分配给 myColumn 的是 0.0 而不是 999999.

And therefore a 0.0 assigned to myColumn instead of 999999.

 String dateTime="CURRENT_DATE_TIME";
 double myColumn;

 Cursor cursor = db.rawQuery("select MyColumn, " +
                "min(strftime('%s', '" + dateTime + "') - strftime('%s', DateTime)) " +
                "from trade1 " +
                "where Judge = 'true' " +
                "and date(DateTime) <= date('" + dateTime + "')", null);

        cursor.moveToFirst();

        myColumn = cursor.getDouble(0);

        if (cursor.getCount()==0) myColumn = 999999;


        cursor.close();

推荐答案

当你使用像 MIN() 这样没有 GROUP BY 的聚合函数时，你会得到所有行的结果，即使没有.

When you use an aggregation function like MIN() without GROUP BY, you get a result over all rows, even if there are none.

只需添加GROUP BY Judge.

(并且在检查有效行之前，您不能尝试从游标中读取.)

(And you must no try to read from the cursor before you have checked for a valid row.)

这篇关于如何在Android rawQuery的SQLite中获取最接近当前时间的行#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！