复合嵌套查询实例

/*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;
    }