高手救命！！ 我在sql中用sum()统计后发现结果被乘上了x倍。300分送上，不够再送。(300分)

很急很急很急很急很急！！！！！！！
我的数据库是SQL SERVER 7。0
我有四张表，分别是 seller（客户代码表） ，seller_type（客户类型表），
stock （客户库存表），sell_daily （销售动态表）
表结构如下
seller /*客户代码表*/ seller_type/*客户类型代码*/
-------------------------- ---------------------------------
seller_type /*客户类型*/ type_code /*类型代码*/
seller_code /*客户代码*/ type_name /*类型名称*/
seller_name /*客户名称*/

stock /*现今库存表*/ sell_daily /*销售动态表*/
--------------------------------- -------------------------------------
seller_code /*客户代码*/ seller_code /*客户代码*/
goods_name /*货物名称*/ goods_name /*货物名称*/
stock /*现今库存量*/ last_stock /*上次剩余库存*/
bad /*库存损坏量*/ now_stock /*现在的库存*/
input /*进货量*/
sell /*销售量*/
bad /*损坏量*/
sell_date /*日期*/

我现在要求查询出每个客户的 进货量，销售量，损坏量，库存数，并作小计

我的sql语句如下
select sell_daily.seller_code as 经销商代码,seller_type.type_name as 经销商类型,
seller.seller_name as 经销商名称,sell_daily.goods_name as 货物名,
sum(input) as 进货量,sum(sell) as 销售量,
sum(sell_daily.bad)as 损坏量,stock.stock as 现库存量
from sell_daily,seller,seller_type,stock
where sell_daily.seller_code=seller.seller_code
and seller.seller_type=seller_type.type_code
and stock.seller_code=sell_daily.seller_code
and stock.goods_name=sell_daily.goods_name
group by sell_daily.seller_code,seller_type.type_name,seller.seller_name,
sell_daily.goods_name,stock.stock
union
select sell_daily.seller_code as 经销商代码,seller_type.type_name as 经销商类型,
seller.seller_name as 经销商名称,'小计' as 货物名,sum(input) as 进货量,
sum(sell_daily.sell) as 销售量,sum(sell_daily.bad)as 损坏量,
sum(stock.stock) as 现库存量
from sell_daily,seller,seller_type,stock
where sell_daily.seller_code=seller.seller_code
and seller.seller_type=seller_type.type_code
and stock.seller_code=sell_daily.seller_code
and stock.goods_name=sell_daily.goods_name
group by sell_daily.seller_code,sell_daily.seller_code,
seller_type.type_name,seller.seller_name
order by sell_daily.seller_code,seller.seller_name

查询出来却发现 结果不知为什么都乘了一定的倍数 请教各位高手这是怎么回事？如何解决？
是不是我的连接有问题

好象GROUP和SUM同时用是有问题的，你的那玩意太长，没时间去看，抱歉！

我觉得应该用临时表来解决这个问题，这么长的SQL语句很容易会把人搞糊涂的，以后要改错都麻烦。

如果我沒說錯的話﹐應該是正常的四倍.
最好對一個表進行sum/group
然后將需要的資料聯起來!

应该用 left join :
SELECT a.seller_code AS 经销商代码, b.type_name AS 经销商类型, b.seller_name
AS 经销商名称, a.货物名, a.进货量, a.销售量, a.损坏量, stock.stock AS 现库存量
FROM (SELECT seller_code, goods_name as 货物名, SUM(input) AS 进货量,
SUM(sell) AS 销售量, SUM(bad) AS 损坏量
FROM sell_daily
GROUP BY seller_code, goods_name) a
LEFT JOIN
(SELECT seller_type.Type_name, seller.seller_code, seller.seller_name,
seller.seller_type
FROM seller LEFT JOIN seller_type
ON (seller_type.type_code = seller.seller_type)) b ON
(b.seller_code = a.seller_code) LEFT JOIN
stock ON (stock.seller_code = a.seller_code) AND (stock.goods_name = a.货物名)
union
SELECT a.seller_code AS 经销商代码, b.type_name AS 经销商类型, b.seller_name
AS 经销商名称, '小计' as 货物名, a.进货量, a.销售量, a.损坏量, stock.stock AS 现库存量
FROM (SELECT seller_code, goods_name as 货物名, SUM(input) AS 进货量,
SUM(sell) AS 销售量, SUM(bad) AS 损坏量
FROM sell_daily
GROUP BY seller_code, goods_name) a
LEFT JOIN
(SELECT seller_type.Type_name, seller.seller_code, seller.seller_name,
seller.seller_type
FROM seller LEFT JOIN seller_type
ON (seller_type.type_code = seller.seller_type)) b ON
(b.seller_code = a.seller_code) LEFT JOIN
stock ON (stock.seller_code = a.seller_code) AND (stock.goods_name = a.货物名)

这样虽然语句复杂一些，但效率应该较高。

>>

你先把group 去掉查询一下看看是不是得到了你想要的资料.如果是的话再进行group应该没
有问题.因为的select出来的资料就已经是原来的四倍,那它group出来的资料肯定也是.
祝你好运.

select sd.seller_code as 经销商代码,seller.seller_name, seller_type.type_name as 经销商名称,sd.goods_name as 货物名,sd.input as 进货量,sd.sell as 销货量,sd.bad as 损坏量,stock.stock as 现库存量
from stock,
seller,seller_type,
(select seller_code,goods_name,sum(input) as input,sum(sell) as sell,sum(bad) as bad from sell_daily group by seller_Code,goods_name)as sd
where stock.seller_code=sd.seller_code and stock.goods_name=sd.goods_name
and sd.seller_code=seller.seller_code and seller.seller_type=seller_type.type_code
union
select sd.seller_code as 经销商代码,seller.seller_name, seller_type.type_name as 经销商名称,'小计' as 货物名,sd.input as 进货量,sd.sell as 销货量,sd.bad as 损坏量,stock.stock as 现库存量
from stock,
seller,seller_type,
(select seller_code,goods_name,sum(input) as input,sum(sell) as sell,sum(bad) as bad from sell_daily group by seller_Code,goods_name)as sd
where stock.seller_code=sd.seller_code and stock.goods_name=sd.goods_name
and sd.seller_code=seller.seller_code and seller.seller_type=seller_type.type_code
order by sd.seller_code,seller.seller_name

stock sell_daily
---------------------- -----------------------------------
seller_code goods_name seller_code goods_name sell_date
1 a 1 a 2001-5-1
1 a 1 a 2001-5-2
1 a 1 a 2001-5-3
1 a 1 a 2001-5-4

你的查询就是这样的原始数据，再Sum，当然就有倍数了。

对于每个计算数量，应该使用 子查询

select
(select sum()
from
where
) as Qty1
,(select sum()
from
where
) as qty2
... ...
from goods_Table
where ...

bbkxjy和ynjinti的答案都可以，
但我想知道为什么会这样
我是个新手，希望各位能说明白一点

你的问题是数据表之间存在多重一对多的关系，所以连接时不能简单用=连接，
根据关系字段在不同数据表中的实际意义，运用LEFT JOIN， RIGHT JOIN

最好把重复的记录去掉
select distinct ...

这么长，不想看了。

假设：
sell_daily stock
-------------------------------- ----------------------
seller_code goods_name input seller_code goods_name stock
1 a 10 1 a 100
1 a 20
你希望的应该是：
seller_code goods_name sum(input) stock
1 a 30 100
而用 where 和 '= '的话，第一步选择（还未 sum) 的结果是：
seller_code goods_name input stock
1 a 10 100
1 a 20 100
再一 sum，Stock 就变成 200 了。因此应该先对 sell_daily 的 input, bad 等 sum，再
左连结 stock。

SELECT a.seller_code AS 经销商代码, b.type_name AS 经销商类型, b.seller_name
AS 经销商名称, a.货物名, a.进货量, a.销售量, a.损坏量, stock.stock AS 现库存量
FROM (SELECT seller_code, goods_name as 货物名, SUM(input) AS 进货量,
SUM(sell) AS 销售量, SUM(bad) AS 损坏量
FROM sell_daily
GROUP BY seller_code, goods_name) a
LEFT JOIN
(SELECT seller_type.Type_name, seller.seller_code, seller.seller_name,
seller.seller_type
FROM seller LEFT JOIN seller_type
ON (seller_type.type_code = seller.seller_type)) b ON
(b.seller_code = a.seller_code) LEFT JOIN
stock ON (stock.seller_code = a.seller_code) AND (stock.goods_name = a.货物名)
union
SELECT a.seller_code AS 经销商代码, b.type_name AS 经销商类型, b.seller_name
AS 经销商名称, '小计' as 货物名, a.进货量, a.销售量, a.损坏量, stock.stock AS 现库存量
FROM (SELECT seller_code, goods_name as 货物名, SUM(input) AS 进货量,
SUM(sell) AS 销售量, SUM(bad) AS 损坏量
FROM sell_daily
GROUP BY seller_code, goods_name) a
LEFT JOIN
(SELECT seller_type.Type_name, seller.seller_code, seller.seller_name,
seller.seller_type
FROM seller LEFT JOIN seller_type
ON (seller_type.type_code = seller.seller_type)) b ON
(b.seller_code = a.seller_code) LEFT JOIN
stock ON (stock.seller_code = a.seller_code) AND (stock.goods_name = a.货物名)

谢谢给位，给分了！！
特别感谢 bbkxjy,guojun,st_cumt,ynjinti,jamlog