如何在Android rawQuery的SQLite中获取最接近当前时间的行# [英] How to get the row# nearest to current time in SQLite for Android rawQuery

问题描述

专家，我想从 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中获取最接近当前时间的行#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！