我有一张有付款表:
worker_id, amount, payed, date
表工人:
id, name, lname
我需要编写SQL,为我提供名称,名称和总和,分别为6月,7月,8月和9月。
Name | Lname | Sum_JUN | Sum_JULY | Sum_AUG | Sum_SEP
我正在尝试子查询,但无法执行。你能帮助我吗?
我创建了SQL(示例)。我将用PHP替换日期。
select w.name, w.lname,
sum(case when p.payed_date between '2014-06-01' and '2014-06-31' then p.amount else 0 end) `sum_june`,
sum(case when p.payed_date between '2014-07-01' and '2014-07-31' then p.amount else 0 end) `sum_july`,
sum(case when p.payed_date between '2014-08-01' and '2014-08-31' then p.amount else 0 end) `sum_august`,
sum(case when p.payed_date between '2014-09-01' and '2014-09-31' then p.amount else 0 end) `sum_september`,
sum(case when p.payed_date between '2014-10-01' and '2014-10-31' then p.amount else 0 end) `sum_november`
from worker w
left join worker_sum p on(w.id = p.worker_id)
group by w.id
您可以使用条件聚合来获得所需的总和,但这将为您提供表中存在的所有年份的月份总和
select w.*,
sum(case when month(p.date) = 6 then p.amount else 0 end) `sum_june`,
sum(case when month(p.date) = 7 then p.amount else 0 end) `sum_july`,
sum(case when month(p.date) = 8 then p.amount else 0 end) `sum_august`,
sum(case when month(p.date) = 9 then p.amount else 0 end) `sum_september`
from workers w
left join payment p on(w.id = p.worker_id)
group by w.id
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句