Android SQLite 查询分组依据和排序依据

Question

我使用微调器在 ListView 中插入数据并选择所需的年份并根据所选日期可视化数据。现在我想按年分组数据而不是按月分隔。我应该改变什么？这是代码:

private void showDetails(String anno){
    SQLiteDatabase db = new BilancioHelper(this).getReadableDatabase();
    final List<Dettaglio> dettagli = new ArrayList<Elenco_cat_entrate.Dettaglio>();

    for (int i=1; i<=12; i++){
        String mese;
        if (i<10){
            mese = "0"+i;
        } else {
            mese = ""+i;
        }
        String sql ="SELECT Categoria, SUM(Entrata) FROM Giornate WHERE entrata>0 AND data LIKE '"+anno+"-%' GROUP BY Categoria";
        Cursor c = db.rawQuery(sql, null);

        while (c.moveToNext()){
            Dettaglio d = new Dettaglio();

            d.categorie = c.getString(0);
            d.entrate = c.getFloat(1);


            dettagli.add(d);
        }
        c.close();
    }

    db.close();

    ListAdapter adapter = new ArrayAdapter<Dettaglio>(this, R.layout.dettaglio_categorie_entrate, R.id.tv_totale_group, dettagli){

        @Override
        public View getView(int position, View convertView, ViewGroup parent) {
            View row = super.getView(position, convertView, parent);


            TextView tvEntrata, tvCategoria;
            tvEntrata = (TextView) row.findViewById(R.id.tv_totale_group);

            tvCategoria = (TextView) row.findViewById(R.id.tv_categorie_group);


            Dettaglio d = dettagli.get(position);

            tvCategoria.setText(d.categorie+"");
            tvEntrata.setText(d.entrate+"");

            return row;
        }
    };

    lista.setAdapter(adapter);

}

Answer 1

只保留 mese 部分。 like 子句中的 % 是任何字符的通配符。

    String sql = "SELECT Categoria, SUM(Entrata) FROM Giornate WHERE entrata>0 AND data LIKE '"+anno+"-%' GROUP BY Categoria";

我还要对输出进行排序:

    String sql = "SELECT Categoria, SUM(Entrata) FROM Giornate WHERE entrata>0 AND data LIKE '"+anno+"-%' GROUP BY Categoria ORDER BY 2 DESC";

按 sum(entrata) 排序，总和最高的优先。或者:

    String sql = "SELECT Categoria, SUM(Entrata), MIN(data) FROM Giornate WHERE entrata>0 AND data LIKE '"+anno+"-%' GROUP BY Categoria ORDER BY 3 DESC";

按日期排序，最新日期在前。