Android SQLiteDatabase.query()GROUP BY/HAVING有多个列不起作用 [英] Android SQLiteDatabase.query() GROUP BY / HAVING with more than one column not working

问题描述

在Android中，我有一个表来存储纬度和经度值，以及与每一行关联的对应ID.当我使用查询

In Android I am having a table which stores latitude and longitude values, along with a corresponding id that is associated with every row. when i am using the query

SELECT latitude, longitude, COUNT(*) count
FROM tasks
GROUP BY latitude, longitude
HAVING count > 1

这只是按纬度而不是经度给我分组.我怎样才能做到这一点?我只想获取具有唯一的经纬度值的任务计数.有什么办法可以使用Android SQLite实现这一目标?例如，表中有此数据...

It is giving me group by latitude only and not longitude. how can I achieve this? I want to get the count of tasks with unique lat long values only. is there any way to achieve this using Android SQLite? For example, with this data in the table...

lat   lng   id
12    34    123
12    34    143
12    35    147
11    35    412

...对于12 lat 34 lng，我必须得到123、143，即算为2 对于其他人，我应该算为1

... for 12 lat 34 lng I must get 123, 143 i.e. count as 2 and for others I should get count as 1

推荐答案

正在重新创建场景...

Recreating the scenario...

CREATE TABLE tasks 
    (
     lat integer, 
     lng integer, 
     id  integer
    );

INSERT INTO  tasks (lat, lng, id) VALUES (12, 34, 123);
INSERT INTO  tasks (lat, lng, id) VALUES (12, 34, 143);
INSERT INTO  tasks (lat, lng, id) VALUES (12, 35, 147);
INSERT INTO  tasks (lat, lng, id) VALUES (11, 35, 412);

并使用指定的查询进行选择(注意，列名与数据示例中的表相匹配)

And selecting with the query specified (note column names match table in data example)

SELECT lat, lng, COUNT(*) count FROM tasks
GROUP BY lat, lng HAVING count > 1

给出以下内容:

lat   lng   count
12    34    2

...这与您期望的一致，除了对于其他人，我应该算为1".要解决该问题并获取所有行，请删除HAVING count > 1，产生

...which is consistent with what you expect, except for "and for others i should get count as 1". To address that, and get all rows, remove the HAVING count > 1, yielding

lat   lng   count
11    35    1
12    34    2
12    35    1

如果使用SQLiteDatabase.query()执行SQL时遇到问题，请发布带有输出(或失败)的代码示例，以便可以诊断问题.

If you're having a problem with the execution of the SQL using SQLiteDatabase.query(), then post a code sample with the output (or failure) so that the problem can be diagnosed.

解决此查询问题

对于Android中的代码，这是一个使用 HAVING 子句.

As for code in Android, here's an example that works using rawQuery. This seems to support the HAVING clause.

SQLiteDatabase db = SQLiteDatabase.create(null);

db = SQLiteDatabase.openDatabase("/data/data/com.mycompany.myapp/databases/myDB.db", null,    SQLiteDatabase.OPEN_READONLY, null);

Cursor cursor = db.rawQuery("SELECT lat, lng, COUNT(*) count  FROM tasks GROUP BY lat, lng  HAVING  count  > 1 ", new String [] {});

// Iterate through cursor
if(cursor.moveToFirst())
{
    do
    {
        Integer lat = cursor.getInt(0);
        Integer lng = cursor.getInt(1);
        Integer count = cursor.getInt(2);

        // Do something here with lat, lng, count

    }
    while(cursor.moveToNext());
}

没有错误处理，我建议使用扩展 SQLiteOpenHelper ，但这应该可以帮助您.

There's no error handling, and I'd suggest using a DB Utilities class that extends SQLiteOpenHelper, but this should get you going.

这篇关于Android SQLiteDatabase.query()GROUP BY/HAVING有多个列不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！