加上一个条件后查出来的数据不准确了!怎么回事?

nitian

SELECT Stock.GoodsID, Goods.GoodsName,
Goods.NormalPrice, SUM(Stock.Stock) AS Stock,
(SUM(Stock.Stock) * Goods.NormalPrice) AS allMoney
FROM Stock, Goods WHERE Stock.GoodsID = Goods.GoodsID
GROUP BY Stock.GoodsID, Goods.GoodsName, Goods.NormalPrice, Stock

上面这样写是对的,我只要加上一个条件, 写成:
SELECT Stock.GoodsID, Goods.GoodsName,
Goods.NormalPrice, SUM(Stock.Stock) AS Stock,
(SUM(Stock.Stock) * Goods.NormalPrice) AS allMoney
FROM Stock, Goods WHERE Stock.GoodsID = Goods.GoodsID AND Stock < 3
GROUP BY Stock.GoodsID, Goods.GoodsName, Goods.NormalPrice, Stock

这样写以后查出来的库存数量就不对了,怎么回事呀?!
STOCK表的结构是:
ID,GoodsID,WarehouseID,Stock

上面的STOCK表中可能会有同一种GOODSID而不同WAREHOUSEID,我觉得可能问题是出在这里.比如:
ID   GoodsID   WarehouseID   Stock
1     g1         w1            10
2     g1         w2             5

谢谢!

waxiaoning

SELECT Stock.GoodsID, Goods.GoodsName,
Goods.NormalPrice, SUM(Stock.Stock) AS Stock,
(SUM(Stock.Stock) * Goods.NormalPrice) AS allMoney
FROM Stock, Goods WHERE Stock.GoodsID = Goods.GoodsID GROUP BY Stock.GoodsID, Goods.GoodsName, Goods.NormalPrice, Stock
having sum( Stock) < 3

waxiaoning

库存数总和小于3
你的方法是单个记录小于3

fanfan1980

条件不同得到的结果肯定不同

pguppgdn

SELECT Stock.GoodsID, Goods.GoodsName,
Goods.NormalPrice, SUM(Stock.Stock) AS Stock,
(SUM(Stock.Stock) * Goods.NormalPrice) AS allMoney
FROM Stock, Goods WHERE Stock.GoodsID = Goods.GoodsID
GROUP BY Stock.GoodsID, Goods.GoodsName, Goods.NormalPrice, Stock
having SUM(Stock.Stock)>3

pguppgdn

写错了要小于3

mydriver

“这样写以后查出来的库存数量就不对了,怎么回事呀?!”

你要的正确数据是什么样子？

nitian

谢谢,又学到了.