就像我在标题中说的那样，我想显示按三个月和年份分组的所有总和（账单），但是如果一个月中没有任何账单，则显示为：第三学期的金额= 0
我的查询（我正在尝试）：
select * from (
select "year",'1er Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 1 and 3 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'2º Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 4 and 6 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'3er Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 7 and 9 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'4º Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 10 and 12 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
)
order by 1 desc, 2 asc
我得到的结果是这样的：
year Trimestre base iva total
2017 1er Trimestre 101 23 124
2016 1er Trimestre 10 2.1 12.1
2016 2º Trimestre 30 6.3 36.3
2016 3er Trimestre 10 2.1 12.1
2016 4º Trimestre 20 4.2 24.1
我真正想要的是：
year Trimestre base iva total
2017 1er Trimestre 101 23 124
2017 2º Trimestre 0 0 0
2017 3er Trimestre 0 0 0
2017 4º Trimestre 0 0 0
2016 1er Trimestre 10 2.1 12.1
2016 2º Trimestre 30 6.3 36.3
2016 3er Trimestre 10 2.1 12.1
2016 4º Trimestre 20 4.2 24.1
感谢您的阅读，我将等待您的帮助:)
提示：1er Trimestre =孕中期，2ºTrimestre =孕中期，3er Trimestre = 3学期，4ºTrimestre = 4学期。
还尝试了以下方法：
select * from (
select "year",'1er Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 1 and 3 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'2º Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 4 and 6 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'3er Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 7 and 9 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'4º Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 10 and 12 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
) as "datos" right outer join (select distinct '1er Trimestre' as "Trimestre" from "Facturas"
union all
select distinct '2º Trimestre' as "Trimestre" from "Facturas"
union all
select distinct '3er Trimestre' as "Trimestre" from "Facturas"
union all
select distinct '4º Trimestre' as "Trimestre" from "Facturas" ) as "trimestres"
on "datos"."Trimestre" = "trimestres"."Trimestre"
order by 1 desc, 2 asc
终于成功了！：
select "year","Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (
select "year",'1er Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 1 and 3 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'2º Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 4 and 6 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'3er Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 7 and 9 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'4º Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 10 and 12 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
(select distinct year("p_fpagado") as "year",'1er Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0
union all
select distinct year("p_fpagado") as "year",'2º Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0
union all
select distinct year("p_fpagado") as "year",'3er Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0
union all
select distinct year("p_fpagado") as "year",'4º Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0)
) group by "year","Trimestre" order by "year" desc,"Trimestre" asc
我制作了另一个“全部联盟”，其后是：
(select distinct year("p_fpagado") as "year",'1er Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0
union all
select distinct year("p_fpagado") as "year",'2º Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0
union all
select distinct year("p_fpagado") as "year",'3er Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0
union all
select distinct year("p_fpagado") as "year",'4º Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0)
)
那是我要获取值的表中存在的所有年份的表。
然后，我将所有内容与“（）”分组，并制成表格以从中选择->“（）”，然后选择以下行：
select "year","Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total"
我知道了。谢谢大家为我提供的帮助！
我希望这个答案对有类似问题的人有所帮助。祝你今天过得愉快 ：）
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句