我有四个表设置如下
产品展示
分支数据
分支机构
我正在尝试创建一个查询,该查询将显示一个表,如下所示
带有以下where子句
我对SQL的了解非常有限,所以想知道如何编写选择查询以选择此数据,我可以获取一个表来显示产品表和“分支数据”表中的数据,但无法获得两个分支的成本价从“分支机构成本计算”表中。
我正在尝试的SQL如下
Select P.ProductCode, P.ProductDescription, BC.CostPrice as B1Cost, BC.CostPrice as B2Cost
From Products P, BranchData BD, BranchCostings BC
Left Join BranchData on BC.BranchCosting = BD.BranchCosting
Where BD.BranchID = Branch1
我一直在研究联接,但这是我遇到的问题,我不知道您可以多次联接一个表,而且我不确定要使用哪个联接(我认为它是左联接)
我刚刚尝试使用以下方法为我提供了类别102中的所有产品,但为每个成本价提供了这些产品
Select P.ProductCode, P.ProductDescription, BC.CostPrice as B1Cost, BC.CostPrice as B2Cost
From Products P, BranchCostings BC
Left Join Products as B0 on B0.BranchCosting = BC.BranchCosting
Left Join BranchData as B1 on B1.BranchCosting = BC.BranchCosting
Left Join BranchData as B2 on B2.BranchCosting = BC.BranchCosting
Where P.C_CATEGORY = 102
现在,我有以下内容在where语句中显示类别中的所有产品,但B1CostPrice和B2CostPrice显示为NULL
Select ProductCode, ProductDescription, B1.CostPrice, B2.CostPrice
From Products
left Join BranchData as B0 on Products.ProductCode = B0.ProductCode
left Join BranchCostings as B1 on B0.BranchCosting = B1.BranchCosting
left Join BranchCostings as B2 on B0.BranchCosting = B2.BranchCosting
Where C_CATEGORY = 102
我尝试更改联接类型以查看是否可以正确显示,但它要么根本不显示数据,要么将Cost Price列显示为NULL来显示正确的产品
数据结构和预期表
产品展示
ProductCode ProductDescription Category
-----------------------------------------
Product1 Product Description 1 102
Product2 Product Description 2 102
Product3 Product Description 3 102
Product4 Product Description 4 102
Product5 Product Description 5 102
Product6 Product Description 6 99
分支数据
Product Code BranchID BranchCosting
Product1 B1 1
Product1 B2 2
Product2 B1 3
Product2 B2 4
Product3 B1 5
Product3 B2 6
Product4 B1 7
Product4 B2 8
Product5 B1 9
Product5 B2 10
Product6 B1 11
Product6 B2 12
分支机构
BranchCosting ListPrice CostPrice
1 2 1
2 4 1
3 6 3
4 8 4
5 10 5
6 12 6
7 14 7
8 16 7
9 18 9
10 20 10
11 22 11
12 24 12
预期表
ProductCode ProductDescription B1CostPrice B2CostPrice
Product2 Product Description 2 3 4
Product3 Product Description 3 5 6
Product5 Product Description 5 9 10
Product6 Product Description 6 11 12
上表中未显示产品1和产品4,因为B1和B2的CostPrice相同
好吧,我终于想通了。
我使用的选择语句如下
SELECT ProductCode, ProductDescription, B1.CostPrice as B1CostPrice, B2CostPrice as B2CostPrice
FROM Products
left JOIN BranchData as B0 on Products.ProductCode = B0.ProductCode
left JOIN BranchData as B9 on Products.ProductCode= B9.ProductCode
left join BranchCostings as B1 on B0.BranchCosting = B1.BranchCosting
left join BranchCostings as B2 on B9.BranchCosting = B2.BranchCosting
Where B0.Branch = Branch1 AND B9.Branch = Branch2 AND Products.Category = 102
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句