2个表 trans_log vip_log 根据2个表的term_no 和电话号码 phone_no 相等的条件做统计
按交易名称统计
- Java code
select (select busi_name from business b where b.busi_code = trim(t2.busi_code)) name, count(*), nvl(sum(t2.amount) / 100, 0), (select count(*) from trans_log t1, vip_log t where t.merch_phone = t1.phone_no and t1.term_trc = t.term_trc and t1.rsp_code = '00' and t1.busi_code = t2.busi_code ) sussess from trans_log t2, vip_log t3 where t3.merch_phone = t2.phone_no and t3.term_trc = t2.term_trc group by t2.busi_code
样式如下
NAME COUNT(*) SUM(T2.AMOUNT)/100 SUCCESS
消费 7 999 5
报送 2 0 2
按交易名称能够统计成功
但是用这个方法做时间统计 success里面的to_char(t2.start_time, 'yyyy-MM-dd')提示不是group by 表达式
按时间统计
- Java code
select to_char(t2.start_time, 'yyyy-MM-dd'), count(*), sum(t2.amount) / 100, (select count(*) from trans_log t1, vip_log t where t.merch_phone = t1.phone_no and t1.term_trc = t.term_trc and t1.rsp_code = '00' and to_char(t1.start_time, 'yyyy-MM-dd') = to_char(t2.start_time, 'yyyy-MM-dd')) sussess from trans_log t2, vip_log t3 where t3.merch_phone = t2.phone_no and t3.term_trc = t2.term_trc group by to_char(t2.start_time, 'yyyy-MM-dd')
各位大神有什么好的方法 不妨指点下小弟！！！！
------解决方案--------------------
- SQL code
--那個帖剛才就不是有人回你了嗎？用case when就可以了count(case when t2.rsp_code='00' then 1 else null end) sussess