ADODB LEFT JOIN结果不正确
（之前一个求助帖子因为脚本2中多了一个select语句，误导了clear_zero，表示抱歉，重新发帖）
现象：ADODB连接数据库执行如下3个脚本，输出的记录个数脚本1和脚本2相差太大（3个脚本都是独立运行的，没有先后顺序）
分析：
1.在自开发的软件中执行3个脚本，对比发现问题出现在LEFT JOIN上?
2.脚本2在WINSQL中执行结果与脚本1一致，是否ADODB的方法有错？
哪位高手指点一下
脚本如下：
脚本1
select distinct StartTime,convert(char,StartTime,112) as DATE,datepart(hh,StartTime) as TIME
into #temp00234ED5A77D20120118151002Time
from pmdb.dbo.tbl_Result_1275072025_3
where
StartTime in ( '2012-01-13 11:00','2012-01-14 11:00','2012-01-15 11:00','2012-01-16 11:00','2012-01-17 11:00','2012-01-18 11:00');
--CELL STATISTICS
select ObjectNo,NeName as BSS,ObjectMemName0 as CELL,hextoint(substring(right(ObjectMemName0,8),1,4)) as LAC,hextoint(right(ObjectMemName0,4)) as CI
into #temp00234ED5A77D20120118151002tbl_CELL_ObjectInstance
from
pmdb.dbo.tbl_ObjectInstance
where
ObjectTypeId=1275069421
and upper( NeName) like '%BSC30696%';
select BSS,CELL,LAC,CI,DATE,TIME,ObjectNo,StartTime into #temp00234ED5A77D20120118151002CELLTime from #temp00234ED5A77D20120118151002tbl_CELL_ObjectInstance,#temp00234ED5A77D20120118151002Time ;
select BSS,CELL,LAC,CI,DATE,TIME
,sum(Counter_1278072512) as C1278072512
into #temp00234ED5A77D20120118151002tbl_Result_1275071420_3
from
#temp00234ED5A77D20120118151002CELLTime as a left join pmdb.dbo.tbl_Result_1275071420_3 as b on (a.ObjectNo=b.ObjectNo and a.StartTime=b.StartTime)
group by BSS,CELL,LAC,CI,DATE,TIME;
select * from #temp00234ED5A77D20120118151002tbl_Result_1275071420_3;
drop table #temp00234ED5A77D20120118151002Time;
drop table #temp00234ED5A77D20120118151002tbl_CELL_ObjectInstance;
drop table #temp00234ED5A77D20120118151002CELLTime;
drop table #temp00234ED5A77D20120118151002tbl_Result_1275071420_3;
记录个数1692个。
脚本2
select distinct StartTime,convert(char,StartTime,112) as DATE,datepart(hh,StartTime) as TIME
into #temp00234ED5A77D20120118151002Time
from pmdb.dbo.tbl_Result_1275072025_3
where
StartTime in ( '2012-01-13 11:00','2012-01-14 11:00','2012-01-15 11:00','2012-01-16 11:00','2012-01-17 11:00','2012-01-18 11:00');
--CELL STATISTICS
select ObjectNo,NeName as BSS,ObjectMemName0 as CELL,hextoint(substring(right(ObjectMemName0,8),1,4)) as LAC,hextoint(right(ObjectMemName0,4)) as CI
into #temp00234ED5A77D20120118151002tbl_CELL_ObjectInstance
from
pmdb.dbo.tbl_ObjectInstance
where
ObjectTypeId=1275069421
and upper( NeName) like '%BSC30696%';
select BSS,CELL,LAC,CI,DATE,TIME,ObjectNo,StartTime into #temp00234ED5A77D20120118151002CELLTime from #temp00234ED5A77D20120118151002tbl_CELL_ObjectInstance,#temp00234ED5A77D20120118151002Time ;
select BSS,CELL,LAC,CI,DATE,TIME
,sum(Counter_1278072512) as C1278072512
from
#temp00234ED5A77D20120118151002CELLTime as a left join pmdb.dbo.tbl_Result_1275071420_3 as b on (a.ObjectNo=b.ObjectNo and a.StartTime=b.StartTime)
group by BSS,CELL,LAC,CI,DATE,TIME;
drop table #temp00234ED5A77D20120118151002Time;
drop table #temp00234ED5A77D20120118151002tbl_CELL_ObjectInstance;
drop table #temp00234ED5A77D20120118151002CELLTime;
记录个数50个。
脚本3
select distinct StartTime,convert(char,StartTime,112) as DATE,datepart(hh,StartTime) as TIME
into #temp00234ED5A77D20120118151002Time
from pmdb.dbo.tbl_Result_1275072025_3
where
StartTime in ( '2012-01-13 11:00','2012-01-14 11:00','2012-01-15 11:00','2012-01-16 11:00','2012-01-17 11:00','2012-01-18 11:00');
--CELL STATISTICS
select ObjectNo,NeName as BSS,ObjectMemName0 as CELL,hextoint(substring(right(ObjectMemName0,8),1,4)) as LAC,hextoint(right(ObjectMemName0,4)) as CI
into #temp00234ED5A77D20120118151002tbl_CELL_ObjectInstance
from
pmdb.dbo.tbl_ObjectInstance
where
ObjectTypeId=1275069421
and upper( NeName) like '%BSC30696%';
select BSS,CELL,LAC,CI,DATE,TIME,ObjectNo,StartTime into #temp00234ED5A77D20120118151002CELLTime from #temp00234ED5A77D20120118151002tbl_CELL_ObjectInstance,#temp00234ED5A77D20120118151002Time ;
select BSS,CELL,LAC,CI,DATE,TIME
,sum(Counter_1278072512) as C1278072512
from
#temp00234ED5A77D20120118151002CELLTime as a,pmdb.dbo.tbl_Result_1275071420_3 as b
where
a.ObjectNo=b.ObjectNo and a.StartTime=b.StartTime
group by BSS,CELL,LAC,CI,DATE,TIME;
drop table #temp00234ED5A77D20120118151002Time;
drop table #temp00234ED5A77D20120118151002tbl_CELL_ObjectInstance;
drop table #temp00234ED5A77D20120118151002CELLTime;
记录个数1688个(与脚本1基本一致，部分无记录的网元没有数据）。
[解决办法]
好长，神马数据库不说下？
[解决办法]
既然你已经判定
2.脚本2在WINSQL中执行结果与脚本1一致，是否ADODB的方法有错？
那么就不应该是left join 的问题啊，如果是left join的问题在sql就不应该相同的
你的脚本相当的复杂我给你一个建议，你可以把你的脚本分开看看返回是不是相同的。
select distinct StartTime,convert(char,StartTime,112) as DATE,datepart(hh,StartTime) as TIME
into #temp00234ED5A77D20120118151002Time
from pmdb.dbo.tbl_Result_1275072025_3
where
StartTime in ( '2012-01-13 11:00','2012-01-14 11:00','2012-01-15 11:00','2012-01-16 11:00','2012-01-17 11:00','2012-01-18 11:00'); --截断到这里，返回数据看看
--CELL STATISTICS
select ObjectNo,NeName as BSS,ObjectMemName0 as CELL,hextoint(substring(right(ObjectMemName0,8),1,4)) as LAC,hextoint(right(ObjectMemName0,4)) as CI
into #temp00234ED5A77D20120118151002tbl_CELL_ObjectInstance
from
pmdb.dbo.tbl_ObjectInstance
where
ObjectTypeId=1275069421
and upper( NeName) like '%BSC30696%'; --对比上面以后截断到这里看看ado返回的数据
select BSS,CELL,LAC,CI,DATE,TIME,ObjectNo,StartTime into #temp00234ED5A77D20120118151002CELLTime from #temp00234ED5A77D20120118151002tbl_CELL_ObjectInstance,#temp00234ED5A77D20120118151002Time ;
select BSS,CELL,LAC,CI,DATE,TIME
,sum(Counter_1278072512) as C1278072512
into #temp00234ED5A77D20120118151002tbl_Result_1275071420_3
from
#temp00234ED5A77D20120118151002CELLTime as a left join pmdb.dbo.tbl_Result_1275071420_3 as b on (a.ObjectNo=b.ObjectNo and a.StartTime=b.StartTime)
group by BSS,CELL,LAC,CI,DATE,TIME; --然后截断到这里
select * from #temp00234ED5A77D20120118151002tbl_Result_1275071420_3; --然后这里
drop table #temp00234ED5A77D20120118151002Time;
drop table #temp00234ED5A77D20120118151002tbl_CELL_ObjectInstance;
drop table #temp00234ED5A77D20120118151002CELLTime;
drop table #temp00234ED5A77D20120118151002tbl_Result_1275071420_3;
