select a.bureau_no,sum(b.main_terminal_cnt) ,
from tcm_user a,tba_tv_card_ask b,tcm_acct_user c,tcm_acct d
where a.user_id=b.user_id
and a.finish_flag='1'
and a.state='1'
and a.user_seq=0
and a.user_id=b.user_id
and b.sequ=0
and a.user_id=c.user_id
and c.state='1'
and c.self_acct_flag='0'
and c.acct_id=d.acct_id
and d.state='1'
and d.PAY_METHOD_NO='21'
group by a.bureau_no
union
select a.bureau_no,sum(b.main_terminal_cnt),sum(decode(sign(main_terminal_cnt-3),1,0,main_terminal_cnt)) "mian_terminal_cnt<3"
from tcm_user a,tba_tv_card_ask b
where a.user_id=b.user_id
and a.finish_flag='1'
and a.state='1'
and a.user_seq=0
and a.user_id=b.user_id
and b.sequ=0
group by a.bureau_no;
from tcm_user a,tba_tv_card_ask b,tcm_acct_user c,tcm_acct d
where a.user_id=b.user_id
and a.finish_flag='1'
and a.state='1'
and a.user_seq=0
and a.user_id=b.user_id
and b.sequ=0
and a.user_id=c.user_id
and c.state='1'
and c.self_acct_flag='0'
and c.acct_id=d.acct_id
and d.state='1'
and d.PAY_METHOD_NO='21'
group by a.bureau_no
union
select a.bureau_no,sum(b.main_terminal_cnt),sum(decode(sign(main_terminal_cnt-3),1,0,main_terminal_cnt)) "mian_terminal_cnt<3"
from tcm_user a,tba_tv_card_ask b
where a.user_id=b.user_id
and a.finish_flag='1'
and a.state='1'
and a.user_seq=0
and a.user_id=b.user_id
and b.sequ=0
group by a.bureau_no;
from tcm_user a,tba_tv_card_ask b,tcm_acct_user c,tcm_acct d
where a.user_id=b.user_id
and a.finish_flag='1'
and a.state='1'
and a.user_seq=0
and a.user_id=b.user_id
and b.sequ=0
and a.user_id=c.user_id
and c.state='1'
and c.self_acct_flag='0'
and c.acct_id=d.acct_id
and d.state='1'
and d.PAY_METHOD_NO='21'
group by a.bureau_no
union
select a.bureau_no,sum(b.main_terminal_cnt),sum(decode(sign(main_terminal_cnt-3),1,0,main_terminal_cnt)) "mian_terminal_cnt<3"
from tcm_user a,tba_tv_card_ask b
where a.user_id=b.user_id
and a.finish_flag='1'
and a.state='1'
and a.user_seq=0
and a.user_id=b.user_id
and b.sequ=0
group by a.bureau_no;
-------------------------------------------------------------------------
000921 44 3 10
000922 55 30 5······main_terminal_cnt2为新加的统计项。
from tcm_user a,tba_tv_card_ask b
where a.user_id=b.user_id
and a.finish_flag='1'
and a.state='1'
and a.user_seq=0
and a.user_id=b.user_id
and b.sequ=0
group by a.bureau_no
这样做是可以达到目的,但是当数据量很大的时候,查询起来会不会很慢??
这样就可以提高查询速度了,。
and c.state='1'
and c.self_acct_flag='0'
and c.acct_id=d.acct_id
and d.state='1'
and d.PAY_METHOD_NO='21'
group by a.bureau_no) main_terminal_cnt2
from
(select a.bureau_no,sum(b.main_terminal_cnt) main_terminal_cnt,sum(decode(sign(main_terminal_cnt-3),1,0,main_terminal_cnt)) "mian_terminal_cnt<3"
from tcm_user a,tba_tv_card_ask b
where a.user_id=b.user_id
and a.finish_flag='1'
and a.state='1'
and a.user_seq=0
and a.user_id=b.user_id
and b.sequ=0
group by a.bureau_no) e
select a.bureau_no,sum(b.main_terminal_cnt),sum(decode(sign(main_terminal_cnt-3),1,0,main_terminal_cnt)) "mian_terminal_cnt<3",(select sum(b.main_terminal_cnt) from tcm_acct_user c,tcm_acct d where a.user_id=c.user_id
and c.state='1'
and c.self_acct_flag='0'
and c.acct_id=d.acct_id
and d.state='1'
and d.PAY_METHOD_NO='21')
from tcm_user a,tba_tv_card_ask b
where a.user_id=b.user_id
and a.finish_flag='1'
and a.state='1'
and a.user_seq=0
and a.user_id=b.user_id
and b.sequ=0
group by a.bureau_no
select bureau_no,main_terminal_cnt,"mian_terminal_cnt<3",(select sum<font color="red">(e.main_terminal_cnt)</font> from tcm_acct_user c,tcm_acct d where e.user_id=c.user_id
and c.state='1'
and c.self_acct_flag='0'
and c.acct_id=d.acct_id
and d.state='1'
and d.PAY_METHOD_NO='21'
group by a.bureau_no) main_terminal_cnt2
from
(select a.bureau_no,sum(b.main_terminal_cnt) main_terminal_cnt,sum(decode(sign(main_terminal_cnt-3),1,0,main_terminal_cnt)) "mian_terminal_cnt<3"
from tcm_user a,tba_tv_card_ask b
where a.user_id=b.user_id
and a.finish_flag='1'
and a.state='1'
and a.user_seq=0
and a.user_id=b.user_id
and b.sequ=0
group by a.bureau_no) e