ADODB执行LEFT JOIN问题
现象：ADODB连接数据库执行如下3个脚本，输出的记录个数脚本1和脚本2相差太大。

分析：
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;
select * from #temp00234ED5A77D20120118151002tbl_Result_1275071420_3;
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基本一致，部分无记录的网元没有数据）。


[解决办法]
两张表的记录不一样多时，左连接与右连接的记录数是不一样的。

[解决办法]

探讨

引用:
两张表的记录不一样多时，左连接与右连接的记录数是不一样的。

拜托看清楚了
脚本1和脚本2的区别是 ：脚本1将结果插入到临时表，再从临时表取出来；脚本2直接将结果输出。两个都用左连接。

[解决办法]
我对比了下你的两个脚本 1和2

1把结果插入到临时表 temp00234ED5A77D20120118151002tbl_Result_1275071420_3 里面然后读出

2 没有插入的过程但是你还是去读 temp00234ED5A77D20120118151002tbl_Result_1275071420_3 所以两个结果不同

因为你的 temp00234ED5A77D20120118151002tbl_Result_1275071420_3 在脚本2中没有被插入自然就不会有更多的数据

你的脚本3结果其实就是你脚本1中用来插入到temp00234ED5A77D20120118151002tbl_Result_1275071420_3的结果


这个和adodb没有关系和left join也没有关系