原意是要找当日a.saleOrder = b.ext1_skill 每个saleOrder 对应的两表最近的记录组合。但是用INNER JOIN...ON,却数目上不是如下的340条记录。求教该如何做?declare @date1 varchar(20), @date2 varchar(20)
set @date1='20100812' set @date2='2010-08-12'
select count(ext1_skill) from ivrlog4ivrDlvInst where start_date=@date1 and se_id='55' and ext1_skill!='' --415..
select count(distinct ext1_skill) from ivrlog4ivrDlvInst where start_date=@date1 and se_id='55' and ext1_skill!='' --340..
select b.caller, b.start_date, b.start_time, b.ext1_skill, a.serviceDate, a.serviceTime, a.workerTel
from ivrlog4ivrDlvInst as b
INNER JOIN order_info as a
ON a.saleOrder = b.ext1_skill and convert(varchar(10),a.instime,112)=@date2 and b.se_id='55' and b.ext1_skill!='' --408..
where b.start_date=@date1 and b.se_id='55' and b.ext1_skill!='' --362..select b.caller, b.start_date, b.start_time, b.ext1_skill, a.serviceDate, a.serviceTime, a.workerTel
from ivrlog4ivrDlvInst as b
INNER JOIN order_info as a
ON a.saleOrder = b.ext1_skill and convert(varchar(10),a.instime,112)=@date2 and b.se_id='55' and b.ext1_skill!=''
and b.start_time=(select max(start_time) from ivrlog4ivrDlvInst where start_date=@date1 and ext1_skill=b.ext1_skill group by ext1_skill ) --296..
set @date1='20100812' set @date2='2010-08-12'
select count(ext1_skill) from ivrlog4ivrDlvInst where start_date=@date1 and se_id='55' and ext1_skill!='' --415..
select count(distinct ext1_skill) from ivrlog4ivrDlvInst where start_date=@date1 and se_id='55' and ext1_skill!='' --340..
select b.caller, b.start_date, b.start_time, b.ext1_skill, a.serviceDate, a.serviceTime, a.workerTel
from ivrlog4ivrDlvInst as b
INNER JOIN order_info as a
ON a.saleOrder = b.ext1_skill and convert(varchar(10),a.instime,112)=@date2 and b.se_id='55' and b.ext1_skill!='' --408..
where b.start_date=@date1 and b.se_id='55' and b.ext1_skill!='' --362..select b.caller, b.start_date, b.start_time, b.ext1_skill, a.serviceDate, a.serviceTime, a.workerTel
from ivrlog4ivrDlvInst as b
INNER JOIN order_info as a
ON a.saleOrder = b.ext1_skill and convert(varchar(10),a.instime,112)=@date2 and b.se_id='55' and b.ext1_skill!=''
and b.start_time=(select max(start_time) from ivrlog4ivrDlvInst where start_date=@date1 and ext1_skill=b.ext1_skill group by ext1_skill ) --296..
两个表都没搞定,其实我是要组合三个表的行记录字段,求教各位方家~
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
--sp_help ivrlog4ivrDlvInst
id int no 4
caller varchar no 50
start_date varchar no 50
start_time varchar no 50
se_id tinyint no 1
ext1_skill varchar no 100 --sp_help order_info
item int no 4
saleOrder varchar no 25
serviceDate varchar no 25
serviceTime varchar no 25
workerTel varchar no 25
数据我贴不了那么多呀:(
正确结果我也不清楚该如何找出来呢。
e.g.:select b.caller, b.start_date, max(b.start_time) as start_time, b.ext1_skill,
c.deliveryNo, c.destroyResult, c.deliveryDate, c.deliveryTime, c.arrangeTime, c.driverPhone, c.driverMobile,
a.serviceDate, a.serviceTime, a.workerTel
into ivrDItemp2
from ivrlog4ivrDlvInst as b
LEFT OUTER JOIN delivery_info as c
--INNER JOIN delivery_info as c
ON c.saleOrder = b.ext1_skill and convert(varchar(10),c.instime,112)=@date2 and b.se_id='55' and b.ext1_skill!=''
LEFT OUTER JOIN order_info as a
--INNER JOIN order_info as a
ON a.saleOrder = b.ext1_skill and convert(varchar(10),a.instime,112)=@date2 and b.se_id='55' and b.ext1_skill!='' --408..where b.start_date=@date1 and b.se_id='55' and b.ext1_skill!='' group by b.caller,b.start_date,b.ext1_skill,a.serviceDate,a.serviceTime,a.workerTel,c.deliveryNo, c.destroyResult, c.deliveryDate, c.deliveryTime, c.arrangeTime, c.driverPhone, c.driverMobile