select b.*,a.Provider,c.Quantity_ck from djb Storage_kc b left join djb a on a.Number=b.ID left join ck1_ls c on b.DJBID = c.DJBID
没看出Storage_kc和ck1_ls的区别在哪里,似乎表结构可以再推敲下。
回1楼,我运行修改后的代码提示:"ck1_ls”附近有语法错误。 select djb.Photo,djb.Number,Storage_kc.*,ck1_ls.Quantity_ck from djb Storage_kc ck1_ls left join djb Storage_kc on djb.Number=Storage_kc.DJBID left join Storage_kc ck1_ls on Storage_kc.DJBID=ck1_ls.DJBID where Storage_kc.GoodsName like '%" + @TextBox6.Text + "%' ORDER BY Storage_kc.GoodsName 你所写到的a.Number=b.ID这两个ID是不等的.
left join djb a on a.Number=b.ID
left join ck1_ls c on b.DJBID = c.DJBID
select djb.Photo,djb.Number,Storage_kc.*,ck1_ls.Quantity_ck
from djb Storage_kc ck1_ls
left join djb Storage_kc on djb.Number=Storage_kc.DJBID
left join Storage_kc ck1_ls on Storage_kc.DJBID=ck1_ls.DJBID
where Storage_kc.GoodsName like '%" + @TextBox6.Text + "%' ORDER BY Storage_kc.GoodsName
你所写到的a.Number=b.ID这两个ID是不等的.
下面这个满足要求了:
select temp.id, temp.djbid, temp.goodsname, temp.spectype, temp.res ,temp.quantity, temp.centralunit,
a.provider ,
temp.quantity_ck
from djb a join
(
select b.id, b.djbid, b.goodsname, b.spectype, b.res ,b.quantity, b.centralunit,
c.quantity_ck
from storage_kc b
left join
ck1_ls c
on b.djbid = c.djbid
)temp
on a.number = temp.djbid
create table djb
(
number int identity not null,
bopomofo_ab varchar(10),
goodsname varchar(20),
provider varchar(20)
);create table storage_kc
(
id int identity,
djbid int not null,
goodsname varchar(20),
spectype varchar(10),
res varchar(10),
quantity int,
centralunit varchar(10)
);create table ck1_ls
(
id int identity,
djbid int not null,
kcid int not null,
goodsname varchar(20),
quantity_ck int,
centralunit varchar(10)
);--查询
select temp.id, temp.djbid, temp.goodsname, temp.spectype, temp.res ,temp.quantity, temp.centralunit,
a.provider ,
temp.quantity_ck
from djb a join
(
select b.id, b.djbid, b.goodsname, b.spectype, b.res ,b.quantity, b.centralunit,
c.quantity_ck
from storage_kc b
left join
ck1_ls c
on b.djbid = c.djbid
)temp
on a.number = temp.djbid;--结果
id djbid goodsname spectype res quantity centralunit provider quantity_ck
1 1 大米 干货 东北 500 斤 阿里巴巴 NULL
2 3 小麦 干货 东北 1000 斤 阿里巴巴 400
3 5 大豆 干货 东北 2000 斤 阿里巴巴 NULL