数据库条件查询语句

需求如下：一张App点击信息的数据库表，App每点击一次，将信息插入表中。要求查询指定时间内的不同包名的App的点击次数

查询语句如下：SELECT DISTINCT PKG ,COUNT(*),* from APP_USAGE_TB_BEAN where DATE > 0 group by PKG

关键词：distinct  去重

            count 计数

            group by 分组

具体方法实现如下：

    /**  * 获取指定时间内的App点击次数  * @param millTimes 单位为s ，当day=0时，是全部的App点击次数  * @return  */  protected static Map<String, Float> getAppClickMap(int millTimes) {Map<String, Float> result = new HashMap<>();
        long time = 0;
        if (millTimes != 0) {time = System.currentTimeMillis() - millTimes;
        }Cursor c = null;
        try {String query = "SELECT DISTINCT "+AppUsageTbBeanDao.Properties.Pkg.columnName +" ,COUNT(*),* from "+AppUsageTbBeanDao.TABLENAME  +" where "+AppUsageTbBeanDao.Properties.Date.columnName +" > "+time+" group by "+AppUsageTbBeanDao.Properties.Pkg.columnName;
//            CbLog.v(TAG,"query sql = "+query);
            c = DaoManager.getAppUsageTbBeanBaseDaoInstance().rawQuery(query);

            if (c != null) {c.moveToFirst();
                for (int i = 0; i < c.getCount(); i++) {String s = "app_click|" + c.getString(c.getColumnIndexOrThrow(AppUsageTbBeanDao.Properties.Pkg.columnName));
                    Float n = c.getFloat(1);
                    result.put(s, n);
                    c.moveToNext();
                }}c.close();
        } catch (Throwable e) {CbLog.e("", e.toString());
        } finally {if (c != null) {c.close();
            }}
//        StringBuffer sb = new StringBuffer("query result: \r\n");
//        Set<String> keySet = result.keySet();
//        for (String key : keySet) {
//            sb.append(key + " : " + result.get(key) + "\r\n");
//        }
//        CbLog.v(TAG, sb.toString());
        return result;
    }