请教写一条SQL写法

[a6567519]

有2张表：WH_InventoryListMG，WH_InventoryListMD。2个是1对多的关系，就是说WH_InventoryListMG表的一条记录可能对应对条WH_InventoryListMD记录。
2张表结构：
WH_InventoryListMG：
ILMID int
ILMSN varchar(14)
WarrantCode varchar(14)
CollectDate datetime
Clerk varchar(50)
Maker varchar(50)
Remark varchar(200)
WH_InventoryListMD：
ILMID int
Sequence int
ProductTypeA int
ProductTypeB int
ProductTypeC int
MNumber int
MUnit varchar(5)
MPrice numeric(12, 2)
MSum numeric(12, 2)
现在是需要查出WH_InventoryListMG中ILMID为1的一条数据的总金额，就是WH_InventoryListMD中ILMID所有为1的MSum总和

[liangCK]

SELECT A.ILMID,SUM(MSum) AS MSum
FROM WH_InventoryListMG AS A
    JOIN WH_InventoryListMD AS B
ON A.ILMID=B.ILMID
WHERE A.ILMID = 1
GROUP BY A.ILMID

[SQL77]

给数据,和结果,

[--小F--]

[Quote=引用 1 楼 liangck 的回复:]
SQL codeSELECT A.ILMID,SUM(MSum)AS MSumFROM WH_InventoryListMGAS AJOIN WH_InventoryListMDAS BON A.ILMID=B.ILMIDWHERE A.ILMID=1GROUPBY A.ILMID
[/Quote]
...

[--小F--]

SELECT 
  A.ILMID,SUM(MSum) AS MSum
FROM 
  WH_InventoryListMG AS A,WH_InventoryListMD AS B
WHERE
  A.ILMID=B.ILMID
AND
  A.ILMID = 1
GROUP BY 
  A.ILMID

[jwdream2008]

select ILMID,sum(Msum) as '总金额' from WH_InventoryListMD where ILMID=1

[a6567519]

上面的方法都可以 ，但现在是有个这个情况就是WH_InventoryListMD表的ProductTypeA，ProductTypeB
，ProductTypeC这3个字段可能不是一样的数据。这样查出来就不对了

[stromboy007]

你这样查询select ILMID,sum(Msum) as '总金额' from WH_InventoryListMD where ILMID=1 跟那个WH_InventoryListMD表的ProductTypeA，ProductTypeB，ProductTypeC这3个字段有什么关系？？

[zaytsing]

select ILMID,sum(Msum) total_money
from WH_InventoryListMD
where ILMID=1
group by ILMID
可以不用到联接啊。。直接汇总就行了。。反正WH_InventoryListMD已经有ILMID字段了。。不用白不用

[zaytsing]

[Quote=引用 5 楼 jwdream2008 的回复:]
SQL codeselect ILMID,sum(Msum)as'总金额'from WH_InventoryListMDwhere ILMID=1
[/Quote]

没用group by编译通的过吗？？？

[snwgija]

select a.ILMID as ID用户,sum(b.MSum) as 总金额
from WH_InventoryListMG a left outer join WH_InventoryListMD b
on a.ILMID=b.ILMID
where a.ILMID='1'
group by a.ILMID

思路上都差不多...LZ,ProductTypeA，ProductTypeB ，ProductTypeC这三个字段和ILMID或是MSum有什么关系?..是不是ID一样,产品种类不一样,金额也会不一样?