[sms] 02_短信数据查询

Android 4.0

previous page next page

短信数据查询

查询：query
case SMS_CONVERSATIONS:
①表

qb.setTables("sms, (SELECT thread_id AS group_thread_id, MAX(date)AS group_date,"

+ "COUNT(*) AS msg_count FROM sms GROUP BY thread_id) AS groups");

②查询条件

qb.appendWhere("sms.thread_id = groups.group_thread_id AND sms.date ="

+ "groups.group_date");

③选择的列

qb.setProjectionMap(sConversationProjectionMap);

break;
select选择的列：

sConversationProjectionMap.put(Sms.Conversations.SNIPPET,"sms.body AS snippet");

sConversationProjectionMap.put(Sms.Conversations.THREAD_ID,"sms.thread_id AS thread_id");

sConversationProjectionMap.put(Sms.Conversations.MESSAGE_COUNT,"groups.msg_count AS msg_count");

sConversationProjectionMap.put("delta", null);

1、groups表
根据每一个联系人，将其最后的发短信的时间，短信的总数选择出来

SELECT  thread_id AS group_thread_id , MAX(date) AS group_date , COUNT(*) AS msg_count 
FROM  sms  GROUP BY  thread_id ;
  
2、sms表，groups表

根据每一个联系人，将最后一条短信，每一个发短信的人总的短信数查询出来

SELECT sms.thread_id AS thread_id, sms.body AS snippet, groups.msg_count AS msg_count 
FROM sms,(SELECT thread_id AS group_thread_id, MAX(date)AS group_date,COUNT(*) AS msg_count FROM sms GROUP BY thread_id) AS groups
WHERE sms.thread_id = groups.group_thread_id AND sms.date = groups.group_date;
  
和手机短信对应：
  

3、自己定义查询的内容

 联系人id，总短信数目，最后一条短信内容，最后一天短信时间，发件人号码
SELECT
      sms.thread_id AS thread_id,groups.msg_count AS msg_count,sms.body AS snippet,sms.date AS date,sms.address AS address     
FROM 
     sms, (SELECT thread_id AS group_thread_id, MAX(date)AS group_date,COUNT(*) AS msg_count FROM sms GROUP BY thread_id) AS groups     
WHERE
     sms.thread_id = groups.group_thread_id and sms.date = groups.group_date

previous page start next page