1.decode()函数,decode(t1.state,'0',1,0) ,如果t1.state='0'返回1,否则返回0;相当于if()-else{}
2.nvl()函数,NVL( string1, replace_with)功能:如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数的都为NULL ,则返回NULL。
例如:nvl(sum(t1.money),0),如果t1.money为null,那么就返回0
3.业务查询统计的 sql:
Java代码
select count(*) as recordTotal,nvl(sum(decode(t1.state,'0',1,0)),0) as successTotal,
nvl(sum(t1.money),0) as moneyTotal,
nvl(sum(decode(t1.state,'0',t1.money,0)),0) as successMoneyTotal,
nvl(sum(decode(t1.state,'0',t1.commission,0)),0) as commissionTotal
from T_PHONE_SALE t1 where 1=1 and t1.state='0' and t1.bussiness_id='15104667195'
and t1.create_date>=to_date('2011-07-27 11:49','yyyy-MM-dd HH24:MI')
and t1.create_date<=to_date('2011-08-26 23:59','yyyy-MM-dd HH24:MI');
======================
假设在客户端,并且如果ActiveX安全设置足够低
var strConnString = "Provider=OraOLEDB.Oracle;Data Source=cpt_d;User ID=john;Password=doe;PLSQLRset=1 "
var conn = new ActiveXObject( "ADODB.Connection ");
conn.open(strConnString);
var rs = conn.execute( "SELECT * FROM Yourtable ");
while (!rs.EOF)
{
for (int i=0; i < rs.fields.count; i++)
{
alert(rs.fields(i).name + ": " + rs.fields(i).value);
}
rs.moveNext();
}
rs.close();
conn.close();
conn = null;
-------------------DONE----------------------