sql 查询MMS_REPORT_STATUS

本文主要是介绍sql 查询MMS_REPORT_STATUS，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

查询 MMS_REPORT_STATUS语句

            case MMS_REPORT_STATUS:/*SELECT DISTINCT address,T.delivery_status AS delivery_status,T.read_status AS read_statusFROM addrINNER JOIN (SELECT P1._id AS id1, P2._id AS id2, P3._id AS id3,ifnull(P2.st, 0) AS delivery_status,ifnull(P3.read_status, 0) AS read_statusFROM pdu P1INNER JOIN pdu P2ON P1.m_id = P2.m_id AND P2.m_type = 134LEFT JOIN pdu P3ON P1.m_id = P3.m_id AND P3.m_type = 136UNIONSELECT P1._id AS id1, P2._id AS id2, P3._id AS id3,ifnull(P2.st, 0) AS delivery_status,ifnull(P3.read_status, 0) AS read_statusFROM pdu P1INNER JOIN pdu P3ON P1.m_id = P3.m_id AND P3.m_type = 136LEFT JOIN pdu P2ON P1.m_id = P2.m_id AND P2.m_type = 134) TON (msg_id = id2 AND type = 151)OR (msg_id = id3 AND type = 137)WHERE T.id1 = ?;*/qb.setTables("addr INNER JOIN (SELECT P1._id AS id1, P2._id" +" AS id2, P3._id AS id3, ifnull(P2.st, 0) AS" +" delivery_status, ifnull(P3.read_status, 0) AS" +" read_status FROM pdu P1 INNER JOIN pdu P2 ON" +" P1.m_id=P2.m_id AND P2.m_type=134 LEFT JOIN" +" pdu P3 ON P1.m_id=P3.m_id AND P3.m_type=136" +" UNION SELECT P1._id AS id1, P2._id AS id2, P3._id" +" AS id3, ifnull(P2.st, 0) AS delivery_status," +" ifnull(P3.read_status, 0) AS read_status FROM" +" pdu P1 INNER JOIN pdu P3 ON P1.m_id=P3.m_id AND" +" P3.m_type=136 LEFT JOIN pdu P2 ON P1.m_id=P2.m_id" +" AND P2.m_type=134) T ON (msg_id=id2 AND type=151)" +" OR (msg_id=id3 AND type=137)");qb.appendWhere("T.id1 = " + uri.getLastPathSegment());qb.setDistinct(true);break;

分解来看，m_type对应X-Mms-Message-Type，134对应DELIVERY_IND,136对应READ_ORIG_IND

    /*** X-Mms-Message-Type field types.*/public static final int MESSAGE_TYPE_SEND_REQ           = 0x80;...public static final int MESSAGE_TYPE_DELIVERY_IND       = 0x86;public static final int MESSAGE_TYPE_READ_REC_IND       = 0x87;public static final int MESSAGE_TYPE_READ_ORIG_IND      = 0x88;

这段语句从新组织类型为delivery/read report 的数据项，m_id一致代表是同一组Mms信息；

新表的有效数据项会同时包含m_type=134时的st，m_type=136时的read_status；

                               SELECT P1._id AS id1, P2._id AS id2, P3._id AS id3,ifnull(P2.st, 0) AS delivery_status,ifnull(P3.read_status, 0) AS read_statusFROM pdu P1INNER JOIN pdu P2ON P1.m_id = P2.m_id AND P2.m_type = 134LEFT JOIN pdu P3ON P1.m_id = P3.m_id AND P3.m_type = 136UNIONSELECT P1._id AS id1, P2._id AS id2, P3._id AS id3,ifnull(P2.st, 0) AS delivery_status,ifnull(P3.read_status, 0) AS read_statusFROM pdu P1INNER JOIN pdu P3ON P1.m_id = P3.m_id AND P3.m_type = 136LEFT JOIN pdu P2ON P1.m_id = P2.m_id AND P2.m_type = 134

然后再去重新组合出对应的address，delivery_status，read_status

                 SELECT DISTINCT address,T.delivery_status AS delivery_status,T.read_status AS read_statusFROM addrINNER JOIN (NEW_TABLE) TON (msg_id = id2 AND type = 151)OR (msg_id = id3 AND type = 137)WHERE T.id1 = ?;

这篇关于sql 查询MMS_REPORT_STATUS的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---