如何组合SQL查询的结果

Question

我正在从两个表中选择数据，并希望合并我的结果。我目前得到以下结果-

Sales | Purchase  | ProName      | ProId
0     | 570       | Capital      | 1
2125  | 0         | Capital      | 1
0     | 100       | Bavaria      | 11
587   | 0         | Bavaria      | 11

我想要的结果是-

Sales | Purchase  | ProName      | ProId
2125  | 570       | Capital      | 1
587   | 100       | Bavaria      | 11

我的SQL查询如下-

SELECT BillDetails.ProId AS Pro, Products.ProdName AS ProName, Brands.BrandName AS BrName,
SUM(BillDetails.quantity) AS quantity, SUM(IIF(Bills.BillType = 1, quantity, 0)) AS Purchase,
SUM(IIF(Bills.BillType = 2, quantity, 0)) AS Sales
FROM BillDetails
INNER JOIN Products ON (Products.ProductId = BillDetails.ProId)
INNER JOIN Brands ON (Brands.BrandId = BillDetails.brand)
GROUP BY ProId, ProdName, BrandName, BillDetails.BillType

Answer 1

下面的SQL查询将返回您所需要的结果-

SELECT BillDetails.ProId AS Pro, Products.ProdName AS ProName, Brands.BrandName AS BrName,
SUM(BillDetails.quantity) AS quantity, SUM(IIF(BillDetails.BillType = 1, quantity, 0)) AS Purchase,
SUM(IIF(BillDetails.BillType = 2, quantity, 0)) AS Sales
FROM BillDetails
INNER JOIN Products ON (Products.ProductId = BillDetails.ProId)
INNER JOIN Brands ON (Brands.BrandId = BillDetails.brand)
GROUP BY ProId, ProdName, BrandName

你几乎是对的，只要从GROUP BY中删除BillDetails.BillType-在你的问题中，我注意到你使用了IIF(Bills.BillType...-根据你提供的信息，我认为这应该是IIF(BillDetails.BillType...?