sql 多表链接查询
a表
id name type
1 张三 采购
2 李四 送货
3 王五 采购
4 赵六 送货
b表
id mzid(a.id) hzid(a.id) jine
1 1 2 100
2 3 4 200
3 1 4 200
要求结果
b.id mzxm hzxm jine
1 张三 李四 100
2 王五 赵六 200
3 张三 赵六 200
[最优解释]
if(object_id('a')is not null)drop table a
go
create table a
(
id int,
[name] varchar(6),
[type] varchar(6)
)
go
insert into a
select 1,'张三','采购' union all
select 2,'李四','送货' union all
select 3,'王五','采购' union all
select 4,'赵六','送货'
go
if(object_id('b')is not null) drop table b
go
create table b
(
id int,
mzid int,
hzid int,
jine decimal(18,4)
)
go
insert into b
select 1,1,2,100 union all
select 2,3,4,200 union all
select 3,1,4,200
go
select b.id as 'b.id',a.name as mzxm,a1.name as hzxm,b.jine
from b inner join a on a.id = b.mzid
inner join a as a1 on a1.id = b.hzid
order by b.id
/*
b.id mzxm hzxm jine
----------- ------ ------ ---------------------------------------
1 张三 李四 100.0000
2 王五 赵六 200.0000
3 张三 赵六 200.0000
(3 行受影响)
*/
