如何写SQL语句（最有效率）

[hubinasm]

1: outward_detail
detail_id detail_outw_id detail_guest_id
1853 746 1584
1854 746 1584
1855 746 1535
1856 799 1999

2:detail_info
info_outw_id info_type info_detail_id
746 汽 1853
746 铁 1855
799 a 1856

如何得到以下的值
detail_id detail_outw_id detail_guest_id info_type
1853 746 1584 汽
1854 746 1584 汽
1855 746 1535 铁
1856 799 1999 a

[wtadminxjeri]

select outward_detail.detail_id,outward_detail.detail_outw_id ,outward_detail.detail_guest_id ,detail_info.info_type from outward_detail inner join detail_info on outward_detail.detail_outw_id =detail_info.info_outw_id

[a1n1]

select o.detail_id,o.detail_outw_id ,o.detail_guest_id ,d.info_type
from outward_detail o inner join detail_info d
on o.detail_id =d.info_detail_id

[hubinasm]

上面两位，不是这么简单的
我要实现的是info_type都能显示出来

[loverpyh]

你的detail_info表里面没有1854对应的info_outw_id啊

[hubinasm]

dloverpyh(龙行天下):是根据detail_outw_id 和detail_guest_id来确定的
因为1853 746 1584
所以1854应该取1853对应的detail_info表中的值

[smartcreater01]

我的徒弟 zjcxc(邹建) 可以帮你搞定

[pisces007]

create table outward_detail
(detail_id int,detail_outw_id int,detail_guest_id int )
insert into outward_detail
select 1853,746,1584
union all select 1854,746,1584
union all select 1855,746,1535
union all select 1856,799,1999

create table detail_info
(info_outw_id int,info_type nvarchar(40),info_detail_id int)
insert into detail_info
select 746,'汽',1853
union all select 746,'铁',1855
union all select 799,'a', 1856

--可以inner join的

select O.detail_id,O.detail_outw_id,O.detail_guest_id,R.info_type
from outward_detail O
join detail_info R on O.detail_id=R.info_detail_id
and O.detail_outw_id =R.info_outw_id
where detail_id in
(select info_detail_id from detail_info where info_outw_id=O.detail_outw_id)

union all

--inner join 不上的
select t2.detail_id ,t2.detail_outw_id,t2.detail_guest_id,R.info_type from
(
select * from outward_detail O
where detail_id in (select info_detail_id from detail_info
where info_outw_id=O.detail_outw_id)
) t1
join
(
select * from outward_detail O
where detail_id not in (select info_detail_id from detail_info
where info_outw_id=O.detail_outw_id)
) t2 on t1.detail_outw_id=t2.detail_outw_id
and t1.detail_guest_id=t2.detail_guest_id
join detail_info R on t1.detail_id=R.info_detail_id
and t1.detail_outw_id =R.info_outw_id
order by detail_id

drop table outward_detail
drop table detail_info


/*测试结果
1853 746 1584 汽
1854 746 1584 汽
1855 746 1535 铁
1856 799 1999 a
*/

[hubinasm]

怎么没人回答啊？

[zjcxc]

昨天看到今天,还是没有看明白关系,所以也无从回答

[1老玉米1]

1854 746 1584 汽

怎么得到？？？？？？

[PYJ2003]

select a.detail_id,a.detail_outw_id,a.detail_guest_id,isnull(b.info_type,c.info_type)
from outward_detail as a
left join detail_info as b
on a.detail_id=b.info_detail_id
left join (
select a.detail_id,b.info_type,a.detail_outw_id,a.detail_guest_id
from outward_detail as a
left join detail_info as b
on a.detail_id=b.info_detail_id
where b.info_type is not null
) as c
on a.detail_guest_id=c.detail_guest_id

这个查询可能会耗费很多系统资源

[hubinasm]

pisces007(蝶鱼) :你的这个也太复杂了吧？

[CSDMN]

写个简单点的

select a.*,b.info_type
from outward_detail a left join detail_info b
on b.info_outw_id =(
select max(info_outw_id)
from detail_info
where info_outw_id=a.detail_outw_id
and info_detail_id<=a.detail_id
)

[CSDMN]

更正和测试：

--建立环境
create table outward_detail (
detail_id int,
detail_outw_id int,
detail_guest_id int
)
go

insert outward_detail
select
1853, 746, 1584
union all select
1854, 746, 1584
union all select
1855, 746, 1535
union all select
1856, 799, 1999
go

create table detail_info (
info_outw_id int,
info_type varchar(20),
info_detail_id int
)
go

insert detail_info
select
746, '汽', 1853
union all select
746, '铁', 1855
union all select
799, 'a', 1856
go

--更正后的查询语句
select a.*,b.info_type
from outward_detail a inner join detail_info b
on a.detail_outw_id=b.info_outw_id
and b.info_detail_id =(
select max(info_detail_id)
from detail_info
where info_outw_id=a.detail_outw_id
and info_detail_id<=a.detail_id
)

/*
测试结果：
detail_id detail_outw_id detail_guest_id info_type
----------- -------------- --------------- --------------------
1853 746 1584 汽
1854 746 1584 汽
1855 746 1535 铁
1856 799 1999 a

（所影响的行数为 4 行）

*/

[CSDMN]

更简单点：
select a.*,b.info_type
from outward_detail a inner join detail_info b
on b.info_detail_id =(
select max(info_detail_id)
from detail_info
where info_outw_id=a.detail_outw_id
and info_detail_id<=a.detail_id
)