假设我有这样一种查询
String sql = "SELECT s.team_id, s.team_name, s.gp, s.w, s.t, s.l, s.go, s.ga, s.score, s.p FROM "
+ "(SELECT team_id, team_name, SUM (gp) gp, SUM (w) w, SUM (t) t, SUM (l) l, SUM (GO) go, SUM (GA) ga, SUM (GO)- SUM (GA) score, SUM (2*w+t) p FROM "
+ "(SELECT t._id team_id, t.name team_name, COUNT(CASE WHEN score_home IS NOT NULL THEN 1 END) gp, COUNT (CASE WHEN score_home > score_away THEN 1 END) w,"
+ " COUNT (CASE WHEN score_home = score_away THEN 1 END) t, COUNT (CASE WHEN score_home < score_away THEN 1 END) l,"
+ " SUM (score_home) go, SUM (score_away) ga"
+ " FROM team_table t LEFT OUTER JOIN match_table m ON m.team_home = t._id"
+ " WHERE t.tournament_id = ? GROUP BY t._id, t.name"
+ " UNION ALL"
+ " SELECT t._id team_id, t.name team_name, COUNT(CASE WHEN score_away IS NOT NULL THEN 1 END) gp, COUNT (CASE WHEN score_home < score_away THEN 1 END) w,"
+ " COUNT (CASE WHEN score_home = score_away THEN 1 END) t, COUNT (CASE WHEN score_home > score_away THEN 1 END) l,"
+ " SUM (score_away) go, SUM (score_home) ga"
+ " FROM team_table t LEFT OUTER JOIN match_table m ON m.team_away = t._id"
+ " WHERE t.tournament_id = ? GROUP BY t._id, t.name)"
+ " GROUP BY team_id, team_name) s"
+ " ORDER BY s.p DESC, s.score DESC, s.go ASC";
然后像这样使用
Cursor cursor = database.rawQuery(sql, args);
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
TeamStats stat = new TeamStats();
stat.setTeamId(cursor.getLong(0));
stat.setTeamName(cursor.getString(1));
stat.setGamesPlayed(cursor.getInt(2));
stat.setWins(cursor.getInt(3));
stat.setTies(cursor.getInt(4));
stat.setLoses(cursor.getInt(5));
stat.setGoalsOwn(cursor.getInt(6));
stat.setGoalsAgaist(cursor.getInt(7));
stat.setScore(cursor.getInt(8));
stat.setPoints(cursor.getInt(9));
stats.add(stat);
cursor.moveToNext();
}
cursor.close();
所以它从许多表中选择值，执行一些操作等等。正如您所看到的，这个查询非常复杂（很难调试），而且性能似乎不如我预期的那么好。我的问题是：
- 我能否使用某种准备好的语句来提高性能？
- 使用更简单的查询并手动处理它们是否会更快速？
EXPLAIN QUERY PLAN是查看sqlite如何处理你的查询的好方法。 - zaplString sql部分是必要的，因为接下来的代码需要使用它。这是我拒绝你的更改的主要原因。你不能只是删除所有周围的代码并期望我接受它。下次，请考虑你的行为并自重。 - user219882