SQL话语求指点！

SQL语句求指点！！
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 

 

查看更多 下一篇