现发现一个怪事..如下:数据库是Oracle 9 i1语句:
SQL>
SQL> --进线
SQL> select
2 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'yyyy') sYear,
3 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'mm') sMonth,
4 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'dd') sDay,
5 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'hh24') sHour,
6 count(distinct(report_call_hist.ani)) IncomeTotal
7 from report_call_hist
8 WHERE report_call_hist.calltype='IN'
9 AND TO_DATE(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss')
10 >=TO_DATE('2007-06-05 00:00:00','YYYY-mm-dd hh24:mi:ss')
11 AND TO_DATE(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss')
12 <TO_DATE('2007-06-07 23:59:59','YYYY-mm-dd hh24:mi:ss')
13 group by to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'yyyy'),
14 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'mm'),
15 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'dd'),
16 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'hh24')
17 order by to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'yyyy'),
18 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'mm'),
19 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'dd'),
20 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'hh24')
21 ;SYEAR SMONTH SDAY SHOUR INCOMETOTAL
----- ------ ---- ----- -----------
2007 06 05 00 295
2007 06 05 01 607
2007 06 05 02 269
2007 06 05 03 1
SQL> 2语句
SQL> --订单金额
SQL> select
2 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'yyyy') ssYear,
3 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'mm') ssMonth,
4 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'dd') ssDay,
5 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'hh24') ssHour,
6 count(*) countNum,
7 nvl(sum(t3.totalprice),0) sumPrice
8 from
9 report_call_hist t1
10 left join phone t2 on t1.ani=concat(concat(nvl(t2.phn1,''),nvl(t2.phn2,'')),nvl(t2.phn2,''))
11 left join Orderhist t3 on t2.contactid= t3.contactid
12 where t1.calltype='IN' AND TO_DATE(t1.datetime,'YYYY-mm-dd hh24:mi:ss')
13 >=TO_DATE('2007-06-05 00:00:00','YYYY-mm-dd hh24:mi:ss')
14 AND TO_DATE(t1.datetime,'YYYY-mm-dd hh24:mi:ss')
15 <TO_DATE('2007-06-07 23:59:59','YYYY-mm-dd hh24:mi:ss')
16 group by to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'yyyy'),
17 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'mm'),
18 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'dd'),
19 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'hh24')
20 order by to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'yyyy'),
21 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'mm'),
22 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'dd'),
23 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'hh24')
24 ;SSYEAR SSMONTH SSDAY SSHOUR COUNTNUM SUMPRICE
------ ------- ----- ------ ---------- ----------
2007 06 05 00 352 0
2007 06 05 01 901 0
2007 06 05 02 335 0
2007 06 05 03 2 0
SQL>
__________________________________
以上大家可以看到不同的SQL语句,返回不同的结果值.
但是在Delphi中,我拖了二个Tadoquery(A和B),连接同一个ADOConnect,A执行1语句,B执行2语句,但是奇怪的时,这二个Adoquery竟然返回的数据一样!!!!程序我能确定,二个都是执行不同的SQL语句,现我将A,B的结果显示在DBGrid,结果如下:
A:
SYEAR SMONTH SDAY SHOUR INCOMETOTAL
2007 06 05 00 295
2007 06 05 01 607
2007 06 05 02 269
2007 06 05 03 1
B:
SSYEAR SSMONTH SSDAY SSHOUR COUNTNUM SUMPRICE
----- ------ ---- ----- -----------
2007 06 05 00 295 0
2007 06 05 01 607 0
2007 06 05 02 269 0
2007 06 05 03 1 0返回的数据竟然和在Pl/SQL里执行返回的结果不一样!!!B返回的结果只是列名和增加一个字段SumPrice,其他的数据和A的结果完全一样,只是列名不一样了,但是在PL/SQL里执行的结果却不一样的,结果在最上面!
真的是怪到家了,我郁闷的要死.....请大家帮忙看看,共同分析下原因!很急的,谢谢大家 了
SQL>
SQL> --进线
SQL> select
2 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'yyyy') sYear,
3 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'mm') sMonth,
4 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'dd') sDay,
5 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'hh24') sHour,
6 count(distinct(report_call_hist.ani)) IncomeTotal
7 from report_call_hist
8 WHERE report_call_hist.calltype='IN'
9 AND TO_DATE(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss')
10 >=TO_DATE('2007-06-05 00:00:00','YYYY-mm-dd hh24:mi:ss')
11 AND TO_DATE(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss')
12 <TO_DATE('2007-06-07 23:59:59','YYYY-mm-dd hh24:mi:ss')
13 group by to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'yyyy'),
14 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'mm'),
15 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'dd'),
16 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'hh24')
17 order by to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'yyyy'),
18 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'mm'),
19 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'dd'),
20 to_char(to_date(report_call_hist.datetime,'YYYY-mm-dd hh24:mi:ss'),'hh24')
21 ;SYEAR SMONTH SDAY SHOUR INCOMETOTAL
----- ------ ---- ----- -----------
2007 06 05 00 295
2007 06 05 01 607
2007 06 05 02 269
2007 06 05 03 1
SQL> 2语句
SQL> --订单金额
SQL> select
2 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'yyyy') ssYear,
3 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'mm') ssMonth,
4 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'dd') ssDay,
5 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'hh24') ssHour,
6 count(*) countNum,
7 nvl(sum(t3.totalprice),0) sumPrice
8 from
9 report_call_hist t1
10 left join phone t2 on t1.ani=concat(concat(nvl(t2.phn1,''),nvl(t2.phn2,'')),nvl(t2.phn2,''))
11 left join Orderhist t3 on t2.contactid= t3.contactid
12 where t1.calltype='IN' AND TO_DATE(t1.datetime,'YYYY-mm-dd hh24:mi:ss')
13 >=TO_DATE('2007-06-05 00:00:00','YYYY-mm-dd hh24:mi:ss')
14 AND TO_DATE(t1.datetime,'YYYY-mm-dd hh24:mi:ss')
15 <TO_DATE('2007-06-07 23:59:59','YYYY-mm-dd hh24:mi:ss')
16 group by to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'yyyy'),
17 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'mm'),
18 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'dd'),
19 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'hh24')
20 order by to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'yyyy'),
21 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'mm'),
22 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'dd'),
23 to_char(to_date(t1.datetime,'YYYY-mm-dd hh24:mi:ss'),'hh24')
24 ;SSYEAR SSMONTH SSDAY SSHOUR COUNTNUM SUMPRICE
------ ------- ----- ------ ---------- ----------
2007 06 05 00 352 0
2007 06 05 01 901 0
2007 06 05 02 335 0
2007 06 05 03 2 0
SQL>
__________________________________
以上大家可以看到不同的SQL语句,返回不同的结果值.
但是在Delphi中,我拖了二个Tadoquery(A和B),连接同一个ADOConnect,A执行1语句,B执行2语句,但是奇怪的时,这二个Adoquery竟然返回的数据一样!!!!程序我能确定,二个都是执行不同的SQL语句,现我将A,B的结果显示在DBGrid,结果如下:
A:
SYEAR SMONTH SDAY SHOUR INCOMETOTAL
2007 06 05 00 295
2007 06 05 01 607
2007 06 05 02 269
2007 06 05 03 1
B:
SSYEAR SSMONTH SSDAY SSHOUR COUNTNUM SUMPRICE
----- ------ ---- ----- -----------
2007 06 05 00 295 0
2007 06 05 01 607 0
2007 06 05 02 269 0
2007 06 05 03 1 0返回的数据竟然和在Pl/SQL里执行返回的结果不一样!!!B返回的结果只是列名和增加一个字段SumPrice,其他的数据和A的结果完全一样,只是列名不一样了,但是在PL/SQL里执行的结果却不一样的,结果在最上面!
真的是怪到家了,我郁闷的要死.....请大家帮忙看看,共同分析下原因!很急的,谢谢大家 了
写这么大堆垃圾出来忽悠人!