我想檢索Price Columnfrom的總和ITEMS TABLE并將其與GOODS TABLE. 問題是當我嘗試加入這些表時，gQnty商品表中的數量列總和給了我84而不是14for DISH A。誰能幫助我如何完成這項任務，我可以通過相同的查詢獲得準確的數量gQnty和價格iPrice。
餐桌用品
gID | gName | gQnty
-------------------------
1 DISH A 10
2 DISH B 12
3 DISH A 4
表項：
iID | gID | iItem | iPrice
--------------------------
1 1 Sugar 200
2 1 Milk 300
3 1 Fruits 100
4 2 Oil 200
5 3 Sugar 100
6 3 Milk 50
7 3 Fruits 40
我的查詢：
select g.gName, sum(g.gQnty)[gQnty], sum(i.iPrice)[iPrice]
from goods g join items i
on g.gID = i.gID
group by g.gName
預期結果：
gName | gQnty | iPrice
----------------------------
DISH A | 14 | 790
DISH B | 12 | 100
uj5u.com熱心網友回復：
左連接是個好主意
WITH A AS (
SELECT gName, SUM(ITEMS.iPrice) AS iPrice
FROM GOODS
LEFT JOIN ITEMS
ON GOODS.gID = ITEMS.gID
GROUP BY gName
)
SELECT gName,
(SELECT SUM(gQnty) FROM GOODS WHERE GOODS.gName = A.gName GROUP BY gName ) AS gQnty,
iPrice
FROM A
小提琴手
uj5u.com熱心網友回復：
試試這個
select g.gName, max(g.gQnty)[gQnty], sum(i.iPrice)[iPrice]
from goods g join items i
on g.gID = i.gID
group by g.gName
甚至
select g.gName, avg(g.gQnty)[gQnty], sum(i.iPrice)[iPrice]
from goods g join items i
on g.gID = i.gID
group by g.gName
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/463813.html