请教一个SQL的写法

0
[待解决问题]

0

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;

0

``````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;
``````
〆灬丶 | 园豆：2287 (老鸟四级) | 2021-09-08 10:17
0

您需要登录以后才能回答，未注册用户请先注册