要求:查询出1-11的字段,其中1、2在一个表order_type ,3、4、5、6、7、8在一个表TB_BAT_ISSUER_MON_STAT,9在TB_CARD_INFO 、TB_ENT_CUSTOMER_ORDER 、TB_ENT_ORDER_FLOW ,10、11在表TB_BAT_TERM_STAT中
目前:这些字段在下面的查询语句中都能查出值
问题:把这些表写在一起后查不出任何值!请各位高手指教,急急急!------1. M01-1 +: 统计 N月份 所有礼品卡的销售面额
select nvl(sum(t.face_value),0) as dis_face_value from TB_ENT_CUSTOMER_ORDER t where t.order_type = 4 and substr(to_char(t.order_date,'yyyyMM'),1,6) = '200910'
------2. N月份 所有充值订单中的充值总额,不含手续费
select nvl(sum(t.face_value),0) as rel_face_value from TB_ENT_CUSTOMER_ORDER t where t.order_type = 1 and substr(to_char(t.order_date,'yyyyMM'),1,6) = '200910'----3. M03-1 -: 统计 N月份,已支付确认的结算单中,充值卡交易的总额。
select nvl(sum(t1.paid_amt),0) as rel_paid_amt from TB_BAT_ISSUER_MON_STAT t1 where t1.product_type = 1 and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')
------4. M03-2 -: ==N月份,已支付确认的结算单中,礼品卡交易的总额。
select nvl(sum(t1.paid_amt),0) as dis_paid_amt from TB_BAT_ISSUER_MON_STAT t1 where t1.product_type = 2 and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')
------5. M03-3 -: ==N月份,已支付确认的结算单中,非本地发卡机构所发行的卡的交易的总额。(交易在该月被结算,不管哪月发生)(结算单中的手续费不算在内)
select nvl(sum(t1.paid_amt),0) as paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month <= to_char(to_date('200910','yyyyMM'),'yyyyMM')
-----6. M04 -: N月份,本发卡机构发行的充值卡在其他发卡机构(系统中发卡机构也代表收单机构)交易的总额。
select nvl(sum(t1.paid_amt + t1.no_paid_amt),0) as rel_paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.product_type = 1 and t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')
----7. M05 -: 份,本发卡机构发行的礼品卡在其他发卡机构(系统中发卡机构也代表收单机构)交易的总额。
select nvl(sum(t1.paid_amt + t1.no_paid_amt),0) as dis_paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.product_type = 2 and t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')
-----8. M07 +: ==N月份,非本地发卡机构所发行的卡的交易的总额。(交易在该月发生,不管是否结算)
select nvl(sum(t1.paid_amt + t1.no_paid_amt),0) as total_paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')-----9. M08 -: 统计在N月前被激活的礼品卡,并且在N月份退回的礼品卡的总额?(eg:2月份以前被激活的卡,在2月份被退回到礼品卡总额)
----------是的。如果当月被激活的礼品卡,当月退回不算在内。
select nvl(sum(t3.card_face_amt),0) as dis_card_amt from TB_CARD_INFO t3,TB_ENT_CUSTOMER_ORDER t,TB_ENT_ORDER_FLOW t5
where t3.product_id = t.product_id
and t.order_id = t5.order_id
and t5.operate_type = 7
and t3.act_stat = 1
and t3.product_type = 2
and t3.act_date < to_char(to_date('200910','yyyyMM'),'yyyyMM')
-------10. M14 -: N月份 充值卡在雅高自己的POS(商户编号小于100)中发生的交易金额。
select nvl(sum(t2.txn_amt),0) as rel_amt from TB_BAT_TERM_STAT t2
where t2.product_type = 1 and t2.mchnt_id < 10100 and substr(t2.settle_date,1,6) = to_char(to_date('200910','yyyyMM'),'yyyyMM')
group by t2.mchnt_id order by t2.mchnt_id-----11. M28 -: 统计 N月份 礼品卡在雅高自己的POS(商户编号小于100)中发生的交易金额。
select nvl(sum(t2.txn_amt),0) as dis_amt from TB_BAT_TERM_STAT t2
where t2.product_type = 2 and t2.mchnt_id < 10100 and substr(t2.settle_date,1,6) = to_char(to_date('200910','yyyyMM'),'yyyyMM')
group by t2.mchnt_id order by t2.mchnt_id这条语句查不出值,不知道为什么?请高手指教!
select t6.dis_face_value,t7.rel_face_value,t8.rel_paid_amt,t9.dis_paid_amt,t10.paid_amt,
t11.rel_paid_amt,t12.dis_paid_amt,t13.total_paid_amt,t14.dis_card_amt,t15.rel_amt,t16.dis_amt
from (select nvl(sum(t.face_value),0) as dis_face_value from TB_ENT_CUSTOMER_ORDER t where t.order_type = 4 and substr(to_char(t.order_date,'yyyyMM'),1,6) = '200910') t6,
(select nvl(sum(t.face_value),0) as rel_face_value from TB_ENT_CUSTOMER_ORDER t where t.order_type = 1 and substr(to_char(t.order_date,'yyyyMM'),1,6) = '200910') t7,
(select nvl(sum(t1.paid_amt),0) as rel_paid_amt from TB_BAT_ISSUER_MON_STAT t1 where t1.product_type = 1 and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')) t8,
(select nvl(sum(t1.paid_amt),0) as dis_paid_amt from TB_BAT_ISSUER_MON_STAT t1 where t1.product_type = 2 and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')) t9,
(select nvl(sum(t1.paid_amt),0) as paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month <= to_char(to_date('200910','yyyyMM'),'yyyyMM')) t10,
(select nvl(sum(t1.paid_amt + t1.no_paid_amt),0) as rel_paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.product_type = 1 and t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')) t11,
(select nvl(sum(t1.paid_amt + t1.no_paid_amt),0) as dis_paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.product_type = 2 and t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')) t12,
(select nvl(sum(t1.paid_amt + t1.no_paid_amt),0) as total_paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')) t13,
(select nvl(sum(t3.card_face_amt),0) as dis_card_amt from TB_CARD_INFO t3,TB_ENT_CUSTOMER_ORDER t,TB_ENT_ORDER_FLOW t5
where t3.product_id = t.product_id
and t.order_id = t5.order_id
and t5.operate_type = 7
and t3.act_stat = 1
and t3.product_type = 2
and t3.act_date < to_char(to_date('200910','yyyyMM'),'yyyyMM')) t14,
(select nvl(sum(t2.txn_amt),0) as rel_amt from TB_BAT_TERM_STAT t2
where t2.product_type = 1 and t2.mchnt_id < 10100 and substr(t2.settle_date,1,6) = to_char(to_date('200910','yyyyMM'),'yyyyMM')
group by t2.mchnt_id order by t2.mchnt_id) t15,
(select nvl(sum(t2.txn_amt),0) as dis_amt from TB_BAT_TERM_STAT t2
where t2.product_type = 2 and t2.mchnt_id < 10100 and substr(t2.settle_date,1,6) = to_char(to_date('200910','yyyyMM'),'yyyyMM')
group by t2.mchnt_id order by t2.mchnt_id) t16;
目前:这些字段在下面的查询语句中都能查出值
问题:把这些表写在一起后查不出任何值!请各位高手指教,急急急!------1. M01-1 +: 统计 N月份 所有礼品卡的销售面额
select nvl(sum(t.face_value),0) as dis_face_value from TB_ENT_CUSTOMER_ORDER t where t.order_type = 4 and substr(to_char(t.order_date,'yyyyMM'),1,6) = '200910'
------2. N月份 所有充值订单中的充值总额,不含手续费
select nvl(sum(t.face_value),0) as rel_face_value from TB_ENT_CUSTOMER_ORDER t where t.order_type = 1 and substr(to_char(t.order_date,'yyyyMM'),1,6) = '200910'----3. M03-1 -: 统计 N月份,已支付确认的结算单中,充值卡交易的总额。
select nvl(sum(t1.paid_amt),0) as rel_paid_amt from TB_BAT_ISSUER_MON_STAT t1 where t1.product_type = 1 and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')
------4. M03-2 -: ==N月份,已支付确认的结算单中,礼品卡交易的总额。
select nvl(sum(t1.paid_amt),0) as dis_paid_amt from TB_BAT_ISSUER_MON_STAT t1 where t1.product_type = 2 and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')
------5. M03-3 -: ==N月份,已支付确认的结算单中,非本地发卡机构所发行的卡的交易的总额。(交易在该月被结算,不管哪月发生)(结算单中的手续费不算在内)
select nvl(sum(t1.paid_amt),0) as paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month <= to_char(to_date('200910','yyyyMM'),'yyyyMM')
-----6. M04 -: N月份,本发卡机构发行的充值卡在其他发卡机构(系统中发卡机构也代表收单机构)交易的总额。
select nvl(sum(t1.paid_amt + t1.no_paid_amt),0) as rel_paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.product_type = 1 and t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')
----7. M05 -: 份,本发卡机构发行的礼品卡在其他发卡机构(系统中发卡机构也代表收单机构)交易的总额。
select nvl(sum(t1.paid_amt + t1.no_paid_amt),0) as dis_paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.product_type = 2 and t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')
-----8. M07 +: ==N月份,非本地发卡机构所发行的卡的交易的总额。(交易在该月发生,不管是否结算)
select nvl(sum(t1.paid_amt + t1.no_paid_amt),0) as total_paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')-----9. M08 -: 统计在N月前被激活的礼品卡,并且在N月份退回的礼品卡的总额?(eg:2月份以前被激活的卡,在2月份被退回到礼品卡总额)
----------是的。如果当月被激活的礼品卡,当月退回不算在内。
select nvl(sum(t3.card_face_amt),0) as dis_card_amt from TB_CARD_INFO t3,TB_ENT_CUSTOMER_ORDER t,TB_ENT_ORDER_FLOW t5
where t3.product_id = t.product_id
and t.order_id = t5.order_id
and t5.operate_type = 7
and t3.act_stat = 1
and t3.product_type = 2
and t3.act_date < to_char(to_date('200910','yyyyMM'),'yyyyMM')
-------10. M14 -: N月份 充值卡在雅高自己的POS(商户编号小于100)中发生的交易金额。
select nvl(sum(t2.txn_amt),0) as rel_amt from TB_BAT_TERM_STAT t2
where t2.product_type = 1 and t2.mchnt_id < 10100 and substr(t2.settle_date,1,6) = to_char(to_date('200910','yyyyMM'),'yyyyMM')
group by t2.mchnt_id order by t2.mchnt_id-----11. M28 -: 统计 N月份 礼品卡在雅高自己的POS(商户编号小于100)中发生的交易金额。
select nvl(sum(t2.txn_amt),0) as dis_amt from TB_BAT_TERM_STAT t2
where t2.product_type = 2 and t2.mchnt_id < 10100 and substr(t2.settle_date,1,6) = to_char(to_date('200910','yyyyMM'),'yyyyMM')
group by t2.mchnt_id order by t2.mchnt_id这条语句查不出值,不知道为什么?请高手指教!
select t6.dis_face_value,t7.rel_face_value,t8.rel_paid_amt,t9.dis_paid_amt,t10.paid_amt,
t11.rel_paid_amt,t12.dis_paid_amt,t13.total_paid_amt,t14.dis_card_amt,t15.rel_amt,t16.dis_amt
from (select nvl(sum(t.face_value),0) as dis_face_value from TB_ENT_CUSTOMER_ORDER t where t.order_type = 4 and substr(to_char(t.order_date,'yyyyMM'),1,6) = '200910') t6,
(select nvl(sum(t.face_value),0) as rel_face_value from TB_ENT_CUSTOMER_ORDER t where t.order_type = 1 and substr(to_char(t.order_date,'yyyyMM'),1,6) = '200910') t7,
(select nvl(sum(t1.paid_amt),0) as rel_paid_amt from TB_BAT_ISSUER_MON_STAT t1 where t1.product_type = 1 and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')) t8,
(select nvl(sum(t1.paid_amt),0) as dis_paid_amt from TB_BAT_ISSUER_MON_STAT t1 where t1.product_type = 2 and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')) t9,
(select nvl(sum(t1.paid_amt),0) as paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month <= to_char(to_date('200910','yyyyMM'),'yyyyMM')) t10,
(select nvl(sum(t1.paid_amt + t1.no_paid_amt),0) as rel_paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.product_type = 1 and t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')) t11,
(select nvl(sum(t1.paid_amt + t1.no_paid_amt),0) as dis_paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.product_type = 2 and t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')) t12,
(select nvl(sum(t1.paid_amt + t1.no_paid_amt),0) as total_paid_amt from TB_BAT_ISSUER_MON_STAT t1
where t1.card_issuer_id <> t1.mchnt_issuer_id and t1.stat_month = to_char(to_date('200910','yyyyMM'),'yyyyMM')) t13,
(select nvl(sum(t3.card_face_amt),0) as dis_card_amt from TB_CARD_INFO t3,TB_ENT_CUSTOMER_ORDER t,TB_ENT_ORDER_FLOW t5
where t3.product_id = t.product_id
and t.order_id = t5.order_id
and t5.operate_type = 7
and t3.act_stat = 1
and t3.product_type = 2
and t3.act_date < to_char(to_date('200910','yyyyMM'),'yyyyMM')) t14,
(select nvl(sum(t2.txn_amt),0) as rel_amt from TB_BAT_TERM_STAT t2
where t2.product_type = 1 and t2.mchnt_id < 10100 and substr(t2.settle_date,1,6) = to_char(to_date('200910','yyyyMM'),'yyyyMM')
group by t2.mchnt_id order by t2.mchnt_id) t15,
(select nvl(sum(t2.txn_amt),0) as dis_amt from TB_BAT_TERM_STAT t2
where t2.product_type = 2 and t2.mchnt_id < 10100 and substr(t2.settle_date,1,6) = to_char(to_date('200910','yyyyMM'),'yyyyMM')
group by t2.mchnt_id order by t2.mchnt_id) t16;
只是要查询的每个字段所给的条件并不相同,除了用子查询,我想不出更好的办法了,还有就是这5张表他们的关联并不大!请高手指教!上面的问题更正:order_type不是表,是TB_ENT_CUSTOMER_ORDER表的字段。对不起!