复合嵌套查询实例

/*SELECT contact.* t2.t1.history.*     FROM contact     INNER JOIN     (SELECT room_contact.contact t1.history.*     FROM     (SELECT room.Id, history.*     FROM room     INNER JOIN history     ON room.room_id = history.to_id     AND history.msg_key IN     (SELECT MAX(msg_key)     FROM history    GROUP BY to_id)) t1     INNER JOIN room_contact     ON t1.Id = room_contact.room) t2     ON contact.Id = t2.contact*/    public static List<Pair<List<Contact>, MessageHistory>> getSessionList() {        StringBuilder sqlBuilder0 = new StringBuilder();    sqlBuilder0.append(" SELECT MAX(msg_key) FROM history")    .append(" WHERE history.removed = 0 AND history.deleted = 0")    .append(" AND history.type <> 'READ_SECRET' AND history.type <> 'INFO'")    .append(" GROUP BY to_id");    Cursor cursor0 = SQLiteUtils.querySql(sqlBuilder0.toString());    SQLiteUtils.printCursor(cursor0);//    StringBuilder sqlBuilder1 = new StringBuilder();//    sqlBuilder1.append("SELECT room.Id, ")//    .append(SQLiteUtils.getColumNames4Select(MessageHistory.class))//    .append(" FROM room INNER JOIN history ON room.room_id = history.to_id")//    .append(" AND history.msg_key IN (SELECT MAX(msg_key) FROM history")//    .append(" WHERE history.removed = 0 AND history.deleted = 0")//    .append(" AND history.type <> 'READ_SECRET' AND history.type <> 'INFO'")//    .append(" GROUP BY to_id) GROUP BY history.to_id ")//    .append(" ORDER BY history.msg_key DESC, history.Id DESC");//    Cursor cursor1 = SQLiteUtils.querySql(sqlBuilder1.toString());//    SQLiteUtils.printCursor(cursor1);    StringBuilder sqlBuilder2 = new StringBuilder();    sqlBuilder2.append("SELECT room_contact.contact, ")    .append(SQLiteUtils.getColumNames4Select(MessageHistory.class, "t1"))    .append(" FROM (SELECT room.Id, ")    .append(SQLiteUtils.getColumNames4Select(MessageHistory.class))    .append(" FROM room INNER JOIN history ON room.room_id = history.to_id")    .append(" AND history.msg_key IN (SELECT MAX(msg_key) FROM history")    .append(" WHERE history.removed = 0 AND history.deleted = 0")    .append(" AND history.type <> 'READ_SECRET' AND history.type <> 'INFO'")    .append(" GROUP BY to_id) GROUP BY history.to_id ")    .append(" ORDER BY history.msg_key DESC, history.Id DESC)")    .append(" t1 LEFT JOIN room_contact")    .append(" ON t1.Id = room_contact.room");    Cursor cursor2 = SQLiteUtils.querySql(sqlBuilder2.toString());    SQLiteUtils.printCursor(cursor2);        StringBuilder sqlBuilder = new StringBuilder();    sqlBuilder.append("SELECT ")    .append(SQLiteUtils.getColumNames4Select(Contact.class)).append(',')    .append(SQLiteUtils.getColumNames4Select(MessageHistory.class, "t2"))    .append(" FROM contact INNER JOIN (SELECT room_contact.contact, ")    .append(SQLiteUtils.getColumNames4Select(MessageHistory.class, "t1"))    .append(" FROM (SELECT room.Id, ")    .append(SQLiteUtils.getColumNames4Select(MessageHistory.class))    .append(" FROM room INNER JOIN history ON room.room_id = history.to_id")    .append(" AND history.msg_key IN (SELECT MAX(msg_key) FROM history")    .append(" WHERE history.removed = 0 AND history.deleted = 0")    .append(" AND history.type <> 'READ_SECRET' AND history.type <> 'INFO'")    .append(" GROUP BY to_id) GROUP BY history.to_id ")    .append(" ORDER BY history.msg_key DESC, history.Id DESC)")    .append(" t1 INNER JOIN room_contact")    .append(" ON t1.Id = room_contact.room) t2 ON contact.Id = t2.contact");        Cursor cursor = SQLiteUtils.querySql(sqlBuilder.toString());    SQLiteUtils.printCursor(cursor);    List<Contact> contacts = SQLiteUtils.processCursor(Contact.class, cursor, null);    List<MessageHistory> messages = SQLiteUtils.processCursor(MessageHistory.class, cursor, "t2");    List<Pair<List<Contact>, MessageHistory>> pairs = new ArrayList<Pair<List<Contact>, MessageHistory>>();    List<MessageHistory> mList = new ArrayList<MessageHistory>();    int count = contacts.size();    for (int i = 0; i < count; i++) {    Contact contact = contacts.get(i);    MessageHistory msgHistory = messages.get(i);    if (mList.contains(msgHistory)) {int index = mList.indexOf(msgHistory);Pair<List<Contact>, MessageHistory> pair = pairs.get(index);List<Contact> cList = pair.first;cList.add(contact);} else {List<Contact> cList = new ArrayList<Contact>();cList.add(contact);mList.add(msgHistory);pairs.add(new Pair<List<Contact>, MessageHistory>(cList, msgHistory));}    }    return pairs;    }