我有这样的查询,我能够分别获取两个表的数据。这个概念是我想要按月分组的金额总和。从任何一个表中,我都可以正确检索金额,但我必须根据月份从两个表中获取金额总和。
(select SUM(amount) as amount
, month(reciept_date) as month
, year(reciept_date) as year
from sg_chool
where academic_year = '2'
group by MONTH(reciept_date))
UNION ALL
(select SUM(amount_paid) as amount
, month(reciept_date) as month
, year(reciept_date) as year
from sg_fees
where academic_year = '2'
group by MONTH(reciept_date))
试试这个
SELECT sum(temp.amount) as amount, temp.month, temp.year
FROM (
(select SUM(amount) as amount
, month(reciept_date) as month
, year(reciept_date) as year
from sg_chool
where academic_year = '2'
group by MONTH(reciept_date))
UNION ALL
(select SUM(amount_paid) as amount
, month(reciept_date) as month
, year(reciept_date) as year
from sg_fees
where academic_year = '2'
group by MONTH(reciept_date))
) as temp
GROUP BY temp.month;
这可能适合您的需求:
SELECT
(school.amount + fees.amount) AS amount
, school.month
, school.year
FROM
(SELECT SUM(amount) AS amount
, month(reciept_date) AS month
, year(reciept_date) AS year
FROM sg_chool
WHERE academic_year = '2'
GROUP BY MONTH(reciept_date)) AS school
JOIN
(SELECT SUM(amount_paid) AS amount
, month(reciept_date) AS month
FROM sg_fees
WHERE academic_year = '2'
GROUP BY MONTH(reciept_date)) AS fees
USING (month)
你可以在这里尝试。