17,086
社区成员
发帖
与我相关
我的任务
分享
SELECT b.*,a.sum FROM a LEFT JOIN(
SELECT
a. CODE,
(
CASE
WHEN t1 IS NULL THEN
0
ELSE
t1
END
) "type为1",
(
CASE
WHEN t0 IS NULL THEN
0
ELSE
t0
END
) "type为0"
FROM
(SELECT * FROM b GROUP BY CODE) a
LEFT JOIN (
SELECT
CODE,
sum(num) t1
FROM
b
WHERE
type = "1"
GROUP BY
CODE
) b ON a. CODE = b. CODE
LEFT JOIN (
SELECT
CODE,
sum(num) t0
FROM
b
WHERE
type = "0"
GROUP BY
CODE
) c ON a. CODE = c. CODE) b on a.code= b.code
我根据A表的code查B表数据了那
select code,sum(decode(type,1,num,0)) as num_1,sum(decode(type,0,num,0)) num_0 from B group by code order by 1;
SELECT
a.*,
(case WHEN t0 is NULL then 0 ELSE t0 END)"type为0"
FROM
(
SELECT
CODE,
sum(num) "type为1"
FROM
b
WHERE
type = "1"
GROUP BY
CODE
) a
LEFT JOIN (
SELECT
CODE,
sum(num) t0
FROM
b
WHERE
type = "0"
GROUP BY
CODE
) b on a.code = b.code