Android ListView -使用group by从SQLite获取其中一列的值

在Android开发中，ListView 是一个常用的控件，用于显示垂直滚动的可变长度的数据列表。当你需要从SQLite数据库中获取数据并按照某一列进行分组显示时，可以使用 GROUP BY 子句来实现。

基础概念

GROUP BY: SQL中的一个子句，用于将查询结果按照一个或多个列进行分组。每个分组会产生一个聚合结果，如 COUNT, SUM, AVG 等。

应用场景

当你需要按照某个属性对数据进行分组，并且对每个分组进行一些聚合操作时，可以使用 GROUP BY。例如，显示每个类别的商品数量。

示例代码

假设我们有一个SQLite数据库，其中有一个名为 products 的表，包含以下列：id, name, category, price。

1. 创建数据库和表

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "products.db";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_PRODUCTS = "products";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_NAME = "name";
    private static final String COLUMN_CATEGORY = "category";
    private static final String COLUMN_PRICE = "price";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTableQuery = "CREATE TABLE " + TABLE_PRODUCTS + " (" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_NAME + " TEXT, " +
                COLUMN_CATEGORY + " TEXT, " +
                COLUMN_PRICE + " REAL)";
        db.execSQL(createTableQuery);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);
        onCreate(db);
    }
}

2. 使用GROUP BY查询数据

public List<String> getCategoriesWithCount() {
    List<String> categoriesWithCount = new ArrayList<>();
    SQLiteDatabase db = this.getReadableDatabase();

    String query = "SELECT category, COUNT(*) as count FROM products GROUP BY category";
    Cursor cursor = db.rawQuery(query, null);

    if (cursor.moveToFirst()) {
        do {
            String category = cursor.getString(cursor.getColumnIndex("category"));
            int count = cursor.getInt(cursor.getColumnIndex("count"));
            categoriesWithCount.add(category + " (" + count + ")");
        } while (cursor.moveToNext());
    }

    cursor.close();
    db.close();

    return categoriesWithCount;
}

3. 在ListView中显示结果

public class MainActivity extends AppCompatActivity {
    private ListView listView;
    private ArrayAdapter<String> adapter;
    private DatabaseHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        listView = findViewById(R.id.listView);
        dbHelper = new DatabaseHelper(this);

        List<String> categoriesWithCount = dbHelper.getCategoriesWithCount();
        adapter = new ArrayAdapter<>(this, android.R.layout.simple_list_item_1, categoriesWithCount);
        listView.setAdapter(adapter);
    }
}