首页 新闻 搜索 专区 学院

请教一个SQL的写法

0
[待解决问题]

如图:请问一个SQL的写法。

取相同数据行的字段乘积再累加。

 

 

大佬们帮帮忙了。

SQL
翩若游龙的主页 翩若游龙 | 初学一级 | 园豆:7
提问于:2021-09-07 18:36
< >
分享
所有回答(4)
0

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

顾晓北 | 园豆:10792 (专家六级) | 2021-09-07 18:44
0

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,,,也有可能是我理解错了

人间春风意 | 园豆:984 (小虾三级) | 2021-09-08 09:05
0

对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;
| 园豆:827 (小虾三级) | 2021-09-08 10:17
0

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

wwr01 | 园豆:630 (小虾三级) | 2021-09-08 14:28
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册