主表A ID, stockid, stackid, productid, amount
数据 0 1 1 1 10
1 3 2 1 1
。。子表stock
id name
数据 1 一号
2 二号
3 三号子表stack
id name
1 A
2 B子表 product
id name
1 产品1
2 产品2主表和个主表id主键列均已经做外键连接希望得到结果为:
ID, stock, stack , productid, amount
数据 0 一号 A 产品1 10
1 三号 B 产品1 1除了用select id,(select name from stock where id=a.stockid) as stock,
(select name from stack where id=a.stackid) as stack,
(select name from product where id=a.productid) as product,amount
from A as a
外还以什么效率更高的方法吗?
数据 0 1 1 1 10
1 3 2 1 1
。。子表stock
id name
数据 1 一号
2 二号
3 三号子表stack
id name
1 A
2 B子表 product
id name
1 产品1
2 产品2主表和个主表id主键列均已经做外键连接希望得到结果为:
ID, stock, stack , productid, amount
数据 0 一号 A 产品1 10
1 三号 B 产品1 1除了用select id,(select name from stock where id=a.stockid) as stock,
(select name from stack where id=a.stackid) as stack,
(select name from product where id=a.productid) as product,amount
from A as a
外还以什么效率更高的方法吗?
from 主表a a
left join stock b on a.stockid=b.id
left join stack c on a.stackid=c.id
left join product d on a.productid=d.id
-- 这也可以满足你的要求吗select a.id, b.stock, c.stack, a.productid, a.amount
from 主表 as a inner join stock as b on a.stockid = b.id
inner join stack as c on a.stackid = c.id
from a a
left join stock b on a.stockid=b.id
left join stack c on a.stackid=c.id
left join product d on a.productid=d.id
select a.id, b.stock, c.stack, d.product, a.amount
from 主表 as a inner join stock as b on a.stockid = b.id
inner join stack as c on a.stackid = c.id
inner join product d on a.productid = d.id
select (case when charindex(cast(A.stockid as varchar(2),cast(stock.id as varchar(2))>0 then name else '' end),
(case when charindex(cast(A.stockid as varchar(2)),cast(stack.id as varchar(2))>0 then name else '' end),
(case when charindex(cast(A.stockid as varchar(2)),cast(product.id as varchar(2))>0 then product else '' end)
from A,stock,stack,product