我用的数据库是postgres,是oracle旗下的一款开源数据库,所以sql语句基本和oracle类似!我的问题是这样的:有三张表,表mt_sms_record,表mo_sms_record和表order_confirm_profile
表mt_sms_record的字段 id serial NOT NULL,
access_no character varying(20) NOT NULL,
msisdn character varying(20) NOT NULL,
sp_product_id character varying(12),
mt_msg_type integer,
insert_time timestamp without time zone NOT NULL
表mo_sms_record的字段 id serial NOT NULL,
access_no character varying(20) NOT NULL,
cmd character varying(100) NOT NULL,
msisdn character varying(20) NOT NULL,
sp_product_id character varying(12),
mo_msg_type integer,
order_method character varying(4),
insert_time timestamp without time zone NOT NULL,
表order_confirm_profile的字段 id serial NOT NULL,
access_no character varying(20) NOT NULL,
cmd character varying(100) NOT NULL,
msisdn character varying(20) NOT NULL,
confirm_access_no character varying(20) NOT NULL,
sp_id character varying(8) NOT NULL,
sp_product_id character varying(12) NOT NULL,
order_time timestamp without time zone NOT NULL,
order_method character varying(4),
其中表mt_sms_record的字段mt_msg_type的值和表mo_sms_record的字段mo_msg_type的值是可以对应上的,这两个表就是信息的上下行表,我需要查询订购短信的轨迹,并按时间排序。我的需求是这样的,通过手机号(msisdn)查出表mt_sms_record的access_no、msisdn、sp_product_id、insert_time的记录,再通过相同的手机号查询出表mo_sms_record的access_no、cmd、msisdn、sp_product_id、insert_time的记录,然后将这两个记录整合起来(cmd无值的为空),再通过access_no到表order_confirm_profile中将sp_id查出,并作为一个字段整合到前两条记录里,并按insert_time排序
注意:一定要排序,因为需求的轨迹。。
急,在线等,谢谢sql数据库oraclepostgres
表mt_sms_record的字段 id serial NOT NULL,
access_no character varying(20) NOT NULL,
msisdn character varying(20) NOT NULL,
sp_product_id character varying(12),
mt_msg_type integer,
insert_time timestamp without time zone NOT NULL
表mo_sms_record的字段 id serial NOT NULL,
access_no character varying(20) NOT NULL,
cmd character varying(100) NOT NULL,
msisdn character varying(20) NOT NULL,
sp_product_id character varying(12),
mo_msg_type integer,
order_method character varying(4),
insert_time timestamp without time zone NOT NULL,
表order_confirm_profile的字段 id serial NOT NULL,
access_no character varying(20) NOT NULL,
cmd character varying(100) NOT NULL,
msisdn character varying(20) NOT NULL,
confirm_access_no character varying(20) NOT NULL,
sp_id character varying(8) NOT NULL,
sp_product_id character varying(12) NOT NULL,
order_time timestamp without time zone NOT NULL,
order_method character varying(4),
其中表mt_sms_record的字段mt_msg_type的值和表mo_sms_record的字段mo_msg_type的值是可以对应上的,这两个表就是信息的上下行表,我需要查询订购短信的轨迹,并按时间排序。我的需求是这样的,通过手机号(msisdn)查出表mt_sms_record的access_no、msisdn、sp_product_id、insert_time的记录,再通过相同的手机号查询出表mo_sms_record的access_no、cmd、msisdn、sp_product_id、insert_time的记录,然后将这两个记录整合起来(cmd无值的为空),再通过access_no到表order_confirm_profile中将sp_id查出,并作为一个字段整合到前两条记录里,并按insert_time排序
注意:一定要排序,因为需求的轨迹。。
急,在线等,谢谢sql数据库oraclepostgres
where c.mt_msg_type=b.mo_msg_type and c.access_no=b.access_no and c.access_no=a.access_no order by c.insert_time
通过access_no到表order_confirm_profile中将sp_id查出,并作为一个字段整合到前两条记录里?不太清楚你的需求了
where c.mt_msg_type=b.mo_msg_type and c.access_no=b.access_no and c.access_no=a.access_no order by c.insert_time