select a.orderno,b.consumer,a.goodsid from 表二 a left join 表一 b on a.orderno=b.orderno
create table poorder(orderno varchar(10), consumer varchar(10)) Insert into poorder select '1111111','cust1' union all select '2222222','cust2'create table poorders(orderno varchar(10), goodsid varchar(10)) Insert into poorders select '1111111','aaaaa' union all select '1111111','bbbbb' union all select '1111111','ccccc' union all select '2222222','ddddd' union all select '2222222','eeeee' union all select '2222222','fffff'select a.orderno,b.consumer,a.goodsid from poorders a left join poorder b on a.orderno=b.orderno--結果 orderno consumer goodsid ------------------------------------- 1111111 cust1 aaaaa 1111111 cust1 bbbbb 1111111 cust1 ccccc 2222222 cust2 ddddd 2222222 cust2 eeeee 2222222 cust2 fffff
/*测试数据*/use one gocreate table poorder ( orderno int, consumer char(50) )create table poorders ( orderno int, goodsid char(50) )insert poorder select 1111111,'cust1' union all select 2222222,'cust2'insert poorders select 1111111,'aaaaa' union all select 1111111,'bbbbb' union all select 1111111,'ccccc' union all select 2222222,'ddddd' union all select 2222222,'eeeee' union all select 2222222,'fffff' select poorder.orderno,poorder.consumer,poorders.goodsid from poorder,poorders where poorder.orderno=poorders.orderno/*测试结果*/ orderno consumer goodsid 1111111 cust1 aaaaa 1111111 cust1 bbbbb 1111111 cust1 ccccc 2222222 cust2 ddddd 2222222 cust2 eeeee 2222222 cust2 fffff
楼上的方法很好,用一个左连接就可以拉!use one go select a.orderno,a.consumer,b.goodsid from poorder a left join poorders bon a.orderno=b.orderno
select a.orderno,b.consumer,a.goodsid from 表二 a left join 表一 b on a.orderno=b.orderno
left join 表一 b
on a.orderno=b.orderno
Insert into poorder
select '1111111','cust1'
union all select '2222222','cust2'create table poorders(orderno varchar(10), goodsid varchar(10))
Insert into poorders
select '1111111','aaaaa'
union all select '1111111','bbbbb'
union all select '1111111','ccccc'
union all select '2222222','ddddd'
union all select '2222222','eeeee'
union all select '2222222','fffff'select a.orderno,b.consumer,a.goodsid from poorders a
left join poorder b
on a.orderno=b.orderno--結果
orderno consumer goodsid
-------------------------------------
1111111 cust1 aaaaa
1111111 cust1 bbbbb
1111111 cust1 ccccc
2222222 cust2 ddddd
2222222 cust2 eeeee
2222222 cust2 fffff
gocreate table poorder
( orderno int,
consumer char(50)
)create table poorders
( orderno int,
goodsid char(50)
)insert poorder select 1111111,'cust1'
union all select 2222222,'cust2'insert poorders select 1111111,'aaaaa'
union all select 1111111,'bbbbb'
union all select 1111111,'ccccc'
union all select 2222222,'ddddd'
union all select 2222222,'eeeee'
union all select 2222222,'fffff'
select poorder.orderno,poorder.consumer,poorders.goodsid from poorder,poorders where poorder.orderno=poorders.orderno/*测试结果*/
orderno consumer goodsid
1111111 cust1 aaaaa
1111111 cust1 bbbbb
1111111 cust1 ccccc
2222222 cust2 ddddd
2222222 cust2 eeeee
2222222 cust2 fffff
go
select a.orderno,a.consumer,b.goodsid from poorder a left join poorders bon a.orderno=b.orderno
left join 表一 b
on a.orderno=b.orderno