请帮忙优化一个查询：关于主从表的一个查询语句，不想group by一堆字段

[phommy]

如下，我为了从子表中取一个sum(金额),group by 了一堆字段，请问有没有别的写法~


SELECT a.ID, a.合同日期, a.原合同号, a.供应商, SUM(采购合同sub.金额) AS 金额,
a.所属工程, a.合同类型, a.到货地址, a.制单, a.审核人1, a.审核人2, a.关闭,
a.备注
FROM 采购合同 a INNER JOIN
采购合同sub ON a.ID = 采购合同sub.ID
GROUP BY a.ID, a.合同日期, a.原合同号, a.供应商, a.所属工程, a.合同类型, a.到货地址,
a.制单, a.审核人1, a.审核人2, a.关闭, a.备注
ORDER BY a.ID

[wangdehao]

---先将从表group by再连接
a.ID, a.合同日期, a.原合同号, a.供应商, b.金额,
a.所属工程, a.合同类型, a.到货地址, a.制单, a.审核人1, a.审核人2, a.关闭,
a.备注
FROM 采购合同 a INNER JOIN
(select Id,金额=sum(金额) from 采购合同sub group by id) b ON a.ID = b.ID
ORDER BY a.ID

[小辉]

SELECT a.ID, SUM(b.金额) AS 金额 ,c.合同日期, c.原合同号, c.供应商,
c.所属工程, c.合同类型, c.到货地址, c.制单, c.审核人1, c.审核人2, c.关闭,
c.备注
FROM 采购合同 a ,采购合同sub b
LEFT OUTER JOIN 采购合同 c on c.id=a.id
WHERE a.ID = b.ID
GROUP BY a.ID

[小辉]

表名写错了

SELECT a.ID, SUM(b.金额) AS 金额 ,c.合同日期, c.原合同号, c.供应商,
c.所属工程, c.合同类型, c.到货地址, c.制单, c.审核人1, c.审核人2, c.关闭,
c.备注
FROM 采购合同 a ,采购合同sub b
LEFT OUTER JOIN 采购合同sub c ON a.ID = c.ID
WHERE a.ID = b.ID
GROUP BY a.ID