[求教]联合查表数据

[山书生]

select * from order_info as a where a.saleOrder=(select b.ext1_skill from ivrlog4ivrDlvInst as b where b.start_date=@date1 and se_id='55' and b.ext1_skill!='') and convert(varchar(10),a.instime,112)=@date2 and max(a.instime)

如上所示，要实现从ivrlog4ivrDlvInst 表找出符合条件的ext1_skill [saleOrder]（多个），凭此saleOrder在order_info 找出对应的数据行。
求教在SQL SERVER里该如何实现？
谢谢 ^_^

[pt1314917]

select * from order_info as a ,ivrlog4ivrDlvInst as b 
where 
a.saleOrder=b.ext1_skill and b.start_date=@date1 and se_id='55' and b.ext1_skill!='') 
and convert(varchar(10),a.instime,112)=@date2 and max(a.instime) --继续

[山书生]

[Quote=引用 1 楼 pt1314917 的回复:]
SQL code

select * from order_info as a ,ivrlog4ivrDlvInst as b
where
a.saleOrder=b.ext1_skill and b.start_date=@date1 and se_id='55' and b.ext1_skill!='')
and convert(varchar(10),a.instime,11……
[/Quote]原来跟oracle的不一样方式，呵呵。

select * from order_info as a ,ivrlog4ivrDlvInst as b 
where 
(a.saleOrder=b.ext1_skill and b.start_date=@date1 and se_id='55' and b.ext1_skill!='') 
and convert(varchar(10),a.instime,112)=@date2 and max(a.instime) --继续

服务器: 消息 147，级别 15，状态 1，行 4
聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

[pt1314917]

[Quote=引用 2 楼 taizans 的回复:]
引用 1 楼 pt1314917 的回复:
SQL code

select * from order_info as a ,ivrlog4ivrDlvInst as b
where
a.saleOrder=b.ext1_skill and b.start_date=@date1 and se_id='55' and b.ext1_skill!='')
and convert(varc……
[/Quote]
你的语句没贴全，先贴全。才好帮你改。

[duanzhi1984]

是哦，我也没看明白。。。

[山书生]

select b.caller, b.start_date, b.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
 from order_info as a ,ivrlog4ivrDlvInst as b ,delivery_info as c
 where 
  a.saleOrder in (select b.ext1_skill from ivrlog4ivrDlvInst where b.start_date=@date1 and b.se_id='55' and b.ext1_skill!='') 
  and convert(varchar(10),a.instime,112)=@date2 
 order by b.start_date desc, b.start_time desc

竟然花了我4min多时间:(

如果此处想以b.ext1_skill仅出现一次，是否可用distinct实现？
(select b.ext1_skill from ivrlog4ivrDlvInst where b.start_date=@date1 and b.se_id='55' and b.ext1_skill!='')

[「已注销」]

select * from order_info as a 
where a.saleOrder=(
	select b.ext1_skill from ivrlog4ivrDlvInst as b 
	where b.start_date=@date1 and se_id='55' 
	and b.ext1_skill!='')
and convert(varchar(10),max(a.instime),112)=@date2

实际传入@date1 和@date2上面的SQL应该是可以执行的。

[「已注销」]

补充一下：“where a.saleOrder=( ”这里面的等号最好用IN，否则很容易出错。

[山书生]

declare 
@date1 varchar(20), 
@date2 varchar(20)	 
set @date1='20100812' 
set @date2='2010-08-12'

select * from order_info as a 
where a.saleOrder=
(select b.ext1_skill from ivrlog4ivrDlvInst as b where b.start_date=@date1 and se_id='55' and b.ext1_skill!='') 
and convert(varchar(10),a.instime,112)=@date2 
and max(a.instime)

tables:
order_info
 saleOrder varchar
 instime   varchar
ivrlog4ivrDlvInst 
 ext1_skill varchar
 start_date varchar
 start_time varchar
 se_id      varchar

[山书生]

原来组合起来，竟然有几W条记录~