我有两个表=借方和简历。我想在这样的1行中将2个表组合在一起
查询是什么?
SELECT lao, SUM(outstanding) as Outstanding, COUNT(lao) as jumlah
FROM debitur
GROUP BY lao
和
SELECT SUM(tgt_pergeseran) as Target
FROM resume
GROUP BY lao
尝试这个 -
SELECT
A.lao,
A.Outstanding,
A.jumla,
B.Target
FROM
(
SELECT lao,
SUM(outstanding) as Outstanding,
COUNT(lao) as jumlah
FROM debitur
GROUP BY lao
)A
INNER JOIN
(
SELECT
lao,
SUM(tgt_pergeseran) as Target
FROM resume
GROUP BY lao
) B
ON A.lao = B.lao
mySQL不支持 full join
。因此,如果您想要两个表中的所有记录 - 即使有些丢失了 - 我建议union all
/ group by
:
SELECT lao, SUM(outstanding) as Outstanding,
COUNT(outstanding) as jumlah,
SUM(target) as target
FROM ((SELECT d.loa, d.outstanding, null as target
FROM debitur d
) UNION ALL
(SELECT r.loa, NULL, r.tgt_pergeseran as target
FROM resume r
)
) rd
GROUP BY lao ;
您可以根据老挝的两个查询之间使用一个联接,但是为此,您需要老挝的子查询。
select
t1.lao, t1.Outstanding, t1.jumlah, t2.Target
from
(select
lao, sum(outstanding) as Outstanding, count(lao) as jumlah
from
debitur
group by
lao)
inner join
(select
lao, sum(tgt_pergeseran) as Target
from
resume
group by
lao) t2 on t2.lao = t1.lao