看了好长时间，总算看明白题意了，似乎能实现，但要是学习还行，要是放程序里去用，emmmm，怕是效率不高。
话说，要是学习，那你就该自己整明白，自己多想想；要是项目里用，写出来的效率能用？

WITH r1 AS (select c.* , sum(a) as sa from (SELECT b.id as bid, b.vala, b.valb,b.number, a.id, a.mainNumber, b.vala * b.valb as a from test.tableb b
left join test.tablea a on a.id = b.aid ) c group by mainNumber)
select r2.bid, r2.vala, r2.valb, r2.number, r2.id, r2.mainNumber , r1.sa as a from
(SELECT b.id as bid, b.vala, b.valb,b.number, a.id, a.mainNumber, b.vala * b.valb as a from test.tableb b
left join test.tablea a on a.id = b.aid) r2
inner join
r1 on r1.mainNumber = r2.mainNumber;

里面的tablea 和 tableb就是你说的a表和b表，但是我这边的数据 跟你show出来的结果 第一列不符合 ，第一列我这边是 1 2 3 4 你那边怎么会是 1 2 3 2，，，也有可能是我理解错了

对2楼格式美化了一下 (千万别 '拿来即用' 🤐)

SELECT
	r2.bid,
	r2.vala,
	r2.valb,
	r2.number,
	r2.aid,
	r2.mainNumber,
	r1.sum_ab AS a
FROM
	(
		SELECT
			b.id AS bid,
			b.vala,
			b.valb,
			b.number,
			a.id AS aid,
			a.mainNumber,
			b.vala * b.valb AS a
		FROM
			b
		LEFT JOIN a ON a.id = b.aid
	) r2
INNER JOIN (
	SELECT
		*, SUM(ab) AS sum_ab
	FROM
		(
			SELECT
				b.id AS bid,
				b.vala,
				b.valb,
				b.number,
				a.id AS aid,
				a.mainNumber,
				b.vala * b.valb AS ab
			FROM
				b
			LEFT JOIN a ON a.id = b.aid
		) AS c
	GROUP BY
		c.mainNumber
) AS r1 ON r1.mainNumber = r2.mainNumber;

先把数据拿到内存，然后在整理数据，是不是比在数据库里面整理好一点呢